Help! Converti decimal em hora MAS os minutos e segundos estão acima de 60

[Deborah Brum]

Pessoal,

 

Preciso da sua ajuda de vcs. Preciso calcular as horas trabalhadas de um equipamento e fiz algumas planilhas em excel (1 por local) e juntei todas em 1.

Trabalho com horas acima de 24h e usei no excel uma coluna formatada em hora/37:50:55. No excel criei uma outra coluna e converti para decimal "celula*24" chamada worked hour DEC e estou fazendo meus calculos no power BI em cima dessa coluna. Não tenho assim como 99,9% dos exemplos uma coluna com data e hora inicial e final pra fazer uma menos a outra. Tenho uma coluna para data abreviada, horas trabalhadas e horas paradas. E criei mais 2 colunas para a conversão direto no excel.

 No PBI, criei uma formula no DAX para conversão de decimal para hora (posto completa no final do texto) e depois formatei como 00:00:00 que esta funcionando no cartão e nos graficos de linha e colunas, entretanto, os minutos e segundos estão acima de 59. As horas aparentemente estão certas e preciso que sejam acumuladas acima de 24h mas os minutos e segundos não podem ultrapassar 59. Tambem preciso fazer o mesmo calculo e os mesmos tipos de graficos para downtime e esta acontecendo a mesma coisa (Consegui fazer funcionar pra worked hours e só mudei a coluna pra downtime). Com essas mesmas medidas tambem preciso fazer calculos de MTTR "MTTR = DIVIDE([Downtime],SUM(Base[Qty of failures]),0)" e MTBR "MTBF = DIVIDE([Worked hours]-[Downtime],[Failures],0)" e tambem estou tendo problemas com minutos e segundos acima de 59h. Alguem poderia me ajudar a identificar onde estou errando? Ja tentei de todas as formas possiveis que pude encontrar na internet mas sempre da errado. Essa medida dax abaixo é a unica que funciona em todas as opções, as outras dão erro ao converter texto em numero ou da direto aquele erro que nem aparece nada no grafico ou cartão. 

Consegui uma formula no primo do chatgpt que ele entende que caso o segundo ou minuto passe de 59 deve ser somado ao minuto ou hora mas na hora de usar em grafico e colocar por mes da erro. O erro que aparece em praticamente todas as tentativas é que não pode converter texto em numero e as vzes no minuto da um numero -1. Em anexo fotos com exemplos. Meu dash esta praticamente pronto, só preciso resolver isso que esta me tirando do serio. Essa mesma medida ou mais de uma se precisar precisa funcionar em cartão e depois em graficos de local/hora e local/mes.

 

MEDIDAS:

Worked hours =

 

var vhour = INT(SUM(Base[Hours worked DEC]))

var vminute = INT((SUM(Base[Hours worked DEC]) - vhour) * 60)

VAR vsecond = ROUND(((sum  (Base[Hours worked DEC]) - vhour ) * 60 - vminute ) * 60,0)

 

var SS = IF(vsecond=0, vsecond & 0, vsecond)

 

var MM = FORMAT(vminute,"00")

 

var HH = FORMAT(vhour,"00")

 

RETURN

VALUE(HH&MM&SS)

 

-----------------------------------------------------------------------------------------------------------------------

Downtime =

 

var vhour = INT(SUM(Base[Downtime DEC]))

var vminute = INT((SUM(Base[Downtime DEC]) - vhour) * 60)

VAR vsecond = ROUND(((sum  (Base[Downtime DEC]) - vhour ) * 60 - vminute ) * 60,0)

 

var SS = IF(vsecond=0, vsecond & 0, vsecond)

 

var MM = FORMAT(vminute,"00")

 

var HH = FORMAT(vhour,"00")

 

RETURN

VALUE(HH & MM & SS)

-----------------------------------------------------------------------------------------------------------------------

MTBF = DIVIDE([Worked hours]-[Downtime],[Failures],0)

-----------------------------------------------------------------------------------------------------------------------

MTBF = DIVIDE([Worked hours]-[Downtime],[Failures],0)

 

Obs:. Ja vi os videos do Leo de horas, do Laender e todos os possiveis do youtube mas todos seguem o data/hora inicial e final. Tento seguir a partir do momento que eles convertem em decimal mas segue dando erro. 

[Joao Raulino]

Perfeito @Deborah Brum!

A visualização de tempo no formato hh:mm:ss talvez seja hoje uma das maiores "gambiarras" praticadas dentro do Power BI, então por isso os resultados não seguem 100% a logica que estamos acostumados a ver no Excel.

De qualquer maneira, existem algumas coisas que podem ser feitas para facilitar conseguirmos o resultado que esperamos. Aqui abaixo algumas dicas:

 

1. Quando trabalhamos com hh:mm:ss no Power BI é sempre melhor ter os valores base em SEGUNDOS para evitar os erros de arredondamento. Para isso você pode multiplicar, no próprio Excel, os valores das colunas "Hours worked DEC" e "Downtime DEC" por 3600.
    
2. Ao calcular a medida MTBF, o correto é primeiro fazer o calculo com os valores base "Hours worked", "Downtime" e "Failure" em suas formas numéricas para somente depois aplicar a formatação em hora, minuto, segundo:

   Citar

   MTBF =

   VAR TotalSecs = DIVIDE(SUMX('Base SEC', 'Base SEC'[Hours worked] - 'Base SEC'[Downtime]), [Failure], 0)

   VAR HH = INT(TotalSecs / 3600)

   VAR MM = INT((TotalSecs - (HH * 3600)) / 60)

   VAR SS = MOD(TotalSecs, 60)

    

   RETURN

       HH * 10000 + MM * 100 + SS

    

3. Ao terminar o calculo do MTBF, deve-se alterar o formato da medida para 00:00:00 pois é isso que vai dar aquele formato hh:mm:ss que queremos ver:
   
    
4. Finalmente, tem que se tomar cuidado ao inserir as medidas num gráfico pois as agregações feitas intrinsicamente pelo Power BI podem dar resultados estranhos para a gente.
   e.g.: a soma 01:00:40 + 00:05:30 vai resultar em 01:05:70 ao invés de 01:06:10 como esperaríamos... a razão disso é que por trás o Power BI esta somando os números 010040 + 000530 = 010570.

 

Aqui abaixo um resultado simplificado, convém aplicar na tua base e ver se ele retorna aquilo que você quer:

Se essa resposta te ajudou, não se esqueça de marcar como melhor solução 😉

Abs!

Editado Dezembro 26, 2023 por Joao Raulino

[Joao Raulino]

Suave @Deborah Brum?

Só uma perguntinha antes de entrarmos em mais detalhes: esses gráficos por acaso são o agregado de vários equipamentos? Ou na verdade eles estão explicitamente filtrados para mostrar apenas um único equipamento?

Abs!

Editado Dezembro 26, 2023 por Joao Raulino

[Deborah Brum]

36 minutos atrás, Joao Raulino disse:

Suave @Deborah Brum?

Só uma perguntinha antes de entrarmos em mais detalhes: esses gráficos por acaso são o agregado de vários equipamentos? Ou na verdade eles estão explicitamente filtrados para mostrar apenas um único equipamento?

Abs!

O total de horas de varios equipamentos.

Exemplo do excel de algumas colunas:

Local, Data, equipment ID, hours worked e downtime (formatados em hora/37:50:55 - Aqui onde os usuarios vão colocar as horas), tipo de manutenção.... e no final oculto hours worked DEC e downtime DEC que é =celula*24 formatado como numero.

[Edson Igari]

@Deborah Brum sugiro também dar uma olhada nesse vídeo gravado pelo Léo:

 

 

[Sandro Vieira]

@Deborah Brum, 

tem este vídeo também do Alisson veja se te ajuda:

 

[Deborah Brum]

Em 26/12/2023 em 20:33, Joao Raulino disse:

Perfeito @Deborah Brum!

A visualização de tempo no formato hh:mm:ss talvez seja hoje uma das maiores "gambiarras" praticadas dentro do Power BI, então por isso os resultados não seguem 100% a logica que estamos acostumados a ver no Excel.

De qualquer maneira, existem algumas coisas que podem ser feitas para facilitar conseguirmos o resultado que esperamos. Aqui abaixo algumas dicas:

 

1. Quando trabalhamos com hh:mm:ss no Power BI é sempre melhor ter os valores base em SEGUNDOS para evitar os erros de arredondamento. Para isso você pode multiplicar, no próprio Excel, os valores das colunas "Hours worked DEC" e "Downtime DEC" por 3600.
    
2. Ao calcular a medida MTBF, o correto é primeiro fazer o calculo com os valores base "Hours worked", "Downtime" e "Failure" em suas formas numéricas para somente depois aplicar a formatação em hora, minuto, segundo:

    

3. Ao terminar o calculo do MTBF, deve-se alterar o formato da medida para 00:00:00 pois é isso que vai dar aquele formato hh:mm:ss que queremos ver:
   
    
4. Finalmente, tem que se tomar cuidado ao inserir as medidas num gráfico pois as agregações feitas intrinsicamente pelo Power BI podem dar resultados estranhos para a gente.
   e.g.: a soma 01:00:40 + 00:05:30 vai resultar em 01:05:70 ao invés de 01:06:10 como esperaríamos... a razão disso é que por trás o Power BI esta somando os números 010040 + 000530 = 010570.

 

Aqui abaixo um resultado simplificado, convém aplicar na tua base e ver se ele retorna aquilo que você quer:

Se essa resposta te ajudou, não se esqueça de marcar como melhor solução 😉

Abs!

Olá, 

Obrigada pelo retorno.

Nã sei se entendi bem o que era pra fazer pq deu errado 😢 Sou inciante então fiquei com medo de criar uma nova coluna pegando o resultado do worked hours DEC e multiplicar por 3600 então fiz direto nas colunas "=([@[Hours worked]]*24)*3600 e =([@Downtime]*24)*3600" que na minha cabeça da no mesmo mas deu um numero absurdamente doido. Como a formula do MTBF tb usa a coluna SEC nem tentei pq antes ja deu errado. 

Será que o erro está vindo na minha tabela? A formatação no excel esta errado? Onde os usuario vão colocar as horas esta formatado em hora/37:50:55 mas no PBI uso só as colunas que foram convertidas em decimal, resultado dessa 1ª. Não sei como converter em segundos depois do decimal no excel sem dar um valor ainda maior como fiz, foto abaixo. Eu não uso as colunas wokrd hours e downtime no PBI pq la leva o formato "data hora" e ao tentar fazer algo nesse campo acaba dando erro. Não encontro exemplos que não seja data e hora inicial e final, acredito que seja isso.

Para MTTR devo fazer uma medida tipo a sua do MTBR? Atual "MTTR = DIVIDE([Downtime],SUM(Base[Qty of failures]),0)"

Me ajuda por favor, ja estou perdendo as esperanças.  😢 

 

 

[Deborah Brum]

Em 26/12/2023 em 23:02, Edson Igari disse:

@Deborah Brum sugiro também dar uma olhada nesse vídeo gravado pelo Léo:

 

 

Ja vi essa aula mas tem data inicial e final assim como todos os outros exemplos do YT. No meu caso eu tenho uma data e o total de horas trabalhadas ou parada por um equipamento e uma outra coluna onde pega o resultado da hora*24 pra converter em decimal. Entendo que da forma do video é mais facil pq tem um sistema que gera essa informação automaticamente, da pra dividir as horas por colunas e até mesmo criar uma coluna para hora, minuto e segundo mas no meu caso é algo mais simples e os dados são incluidos na mão. Exemplo abaixo:

Obs: as demais colunas foram ocultadas.

[Joao Raulino]

Oi @Deborah Brum!

A conversão para segundos seria melhor feita diretamente no Excel ou então no Power Query para evitar erros de arredondamento.

E sim, a medida MTTR vai seguir a mesma logica da MTBF com o uso do SUMX() dentro da DIVIDE().

Se puder compartilha coma gente o .PBIX e um Excel-base simplificado que vai ser mais fácil te ajudar 😉

Abs!

[Edson Igari]

21 minutos atrás, Deborah Brum disse:

Ja vi essa aula mas tem data inicial e final assim como todos os outros exemplos do YT. No meu caso eu tenho uma data e o total de horas trabalhadas ou parada por um equipamento e uma outra coluna onde pega o resultado da hora*24 pra converter em decimal. Entendo que da forma do video é mais facil pq tem um sistema que gera essa informação automaticamente, da pra dividir as horas por colunas e até mesmo criar uma coluna para hora, minuto e segundo mas no meu caso é algo mais simples e os dados são incluidos na mão. Exemplo abaixo:

Obs: as demais colunas foram ocultadas.

@Deborah Brum, as vezes da problema na hora de converter de decimal pra formato hora, pois em decimal um minuto é = 1 e em hora é = 01. Fiz um painel de acompanhamento de horas no ano passado e resolvi da seguinte forma:

print painel.


Veja se consegue adaptar o dax. No meu caso, estou somando as colunas de horas mesmo. Tem uma subtração ao final do sumx, pois no meu caso é o realizado menos o previsto. Se precisar dos segundos, só seguir a mesma lógica das variáveis. Depois é só selecionar a medida e lá no formato colocar: 00:00:00

HoraExtra/Pendente = 

VAR vHorasDecimal = 
SUMX(
    FILTER(
        fEfetividade,
        RELATED(dColeta[fl_justificativa_elegivel]) = 0 && RELATED(dColeta[realizado]) = 1 &&
        NOT(ISBLANK(RELATED(dColeta[tempo_loja])))
    ),
    (RELATED(dColeta[tempo_loja]) - RELATED(dColeta[carga_horaria])) * 24
) 
VAR vHora = INT(vHorasDecimal)
VAR vMinutosDecimal = 60 * (vHorasDecimal - vHora)
VAR vMinutos = INT(vMinutosDecimal)
VAR vM = IF(vMinutos < 0 || vMinutos = 60, 0, vMinutos)
VAR vHH = IF(LEN(vHora ) = 1, "0" & vHora , vHora )
VAR vMM = IF(LEN(vM) = 1, "0" & vM, vM)
RETURN
CONVERT(vHH&vMM,INTEGER)

Editado Dezembro 28, 2023 por Edson Igari

[Deborah Brum]

9 horas atrás, Joao Raulino disse:

Oi @Deborah Brum!

A conversão para segundos seria melhor feita diretamente no Excel ou então no Power Query para evitar erros de arredondamento.

E sim, a medida MTTR vai seguir a mesma logica da MTBF com o uso do SUMX() dentro da DIVIDE().

Se puder compartilha coma gente o .PBIX e um Excel-base simplificado que vai ser mais fácil te ajudar 😉

Abs!

Conseguimos pessoal, João foi pacientemente me ajudando e com alguns ajustes as horas agora aparecem na forma correta. Agradeço o João do fundo do meu coração pela disponibilidade e auxilio e a todos que de alguma forma se disponibilizaram a ajudar tb. Sou inciante e ainda tenho muito a estudar.

Feliz ano novo para todos!