Como trabalhar com HORAS no formato hh:mm:ss | Case de PCP (Produção)

Leonardo Karpinski

Leonardo Karpinski

Mestre do Power BI, criador do Curso Express de Power BI e Curso Completo de Power BI. Formou mais de 16 mil alunos nos últimos anos e participou de projetos em grandes empresas nacionais e multinacionais.

Você já precisou trabalhar com horas no Power BI? Teve dificuldades? Leu o título do post e ficou confuso porque sempre te falaram que não era possível?! Então continue aí porque te mostrarei em detalhes como trabalhar com horas no formato hh:mm:ss, como fazer drill down na dCalendario até chegar em segundos e muito mais!

Cenário

Trabalharemos com um Case de Produção – sugerido por um aluno do curso completo. Iremos trabalhar com uma base de dados fictícia bem simples, apenas para mostrar o procedimento e em Outubro…

[Alerta: contém spoilers] Isso mesmo que você leu! Em Outubro teremos o Power BI Week. Desenvolveremos um Dashboard PCP completaço com cálculo de horas produtivas, improdutivas, OEE entre outros indicadores. Aguardem!

Base de dados

Nossa base de dados consiste de um arquivo em Excel com uma tabela fato chamada fProducao. Ela possui colunas com informações de cada operação realizada por cada operador. Temos duas colunas em formato hh:mm:ss indicando a data/hora inicial e final de cada operação.

Restrições iniciais

O maior problema que identifiquei nesse cenário é tentar trabalhar com esses dados de tempo em formato hh:mm:ss. O PBI não permite isso! Precisaremos trabalhar com decimais !

Dica:
Não é possível fazer soma, média, qualquer tipo de conta com um dado no formato hh:mm:ss no Power BI.

No nosso Canal do Telegram o pessoal também comentou que o PBI não identifica automaticamente a duração em horas quando esse valor passa de 24h. Testaremos exatamente esse exemplo já já!

Figura 1: Importando dados para o PBI

Repare que o PBI automaticamente modificou os tipos de dados e, dessa vez, ficou tudo certinho. Veja que as colunas DataHoraInicial e DataHoraFinal ficaram no formato dd/mm/aaaa hh:mm:ss, beleza?!

Após checar se está tudo certo com nossa base, clique em Fechar e Aplicar.

Em termos de performance, recomendo sempre que você traga a coluna de Data/Hora separada – uma coluna de data e outra de hora porque quanto mais valores distintos uma coluna tiver pior a performance do modelo – dificulta a compressão das colunas.

Quando temos data e hora na mesma coluna o número de combinações possíveis desses dois valores é bem maior – ou seja, teremos mais valores distintos nessa coluna. Sempre tenha isso em mente ao trabalhar no PBI, beleza?!

Dica:
Quanto mais valores distintos tiver uma coluna, menos performático o modelo estará. Recomenda-se dividir a coluna Data/Hora em duas – uma para Data e outra para Hora.


Teste inicial

Lembra da restrição que citamos sobre o PBI não trazer valor de horas corretamente quando ela passa de 24h? Vamos fazer um teste rapidamente para ver o que ocorre, de fato.

Vamos adicionar uma coluna via DAX com as horas decorridas entre a DataHoraInicial e a DataHoraFinal. Adicione uma coluna nova e digite a fórmula a seguir para replicar o exemplo:

Horas decorridas =
fProducao[DataHoraFinal] – fProducao[DataHoraInicial]

Após mudar o tipo para o formato Hora, você perceberá que o PBI não mostra valores acima de 24 horas (o mesmo aconteceria no Excel). No lugar ele só apresenta o restante (minutos e segundos).

Repare o resultado dessa fórmula quando o valor passa de 23:59:59:

Figura 2: Restrição

Precisamos então multiplicar essa diferença por 24 e automaticamente o PBI já vai converter a coluna para o tipo Decimal:

Horas decorridas =
24 * ( fProducao[DataHoraFinal] – fProducao[DataHoraInicial] )


Figura 3: Transformando horas em decimais via DAX

Nossa estratégia será trabalhar no formato decimal e só no final voltar com o formato hh:mm:ss, beleza?!

Esse foi só um teste! Vamos apagar essa coluna e voltar lá para o Power Query para fazer o que tínhamos mencionado: trabalhar com uma coluna de data e outra de hora – para melhorar a performance do modelo.


Tratamento no Power Query

Primeiro faremos esse mesmo cálculo (subtração) no Power Query. Para isso, siga os passos:

Etapas:
1. Clique com o mouse na coluna DataHoraFinal
2. Clique com o mouse na coluna DataHoraInicial
3. Vá em Adicionar coluna e em Hora, selecione Subtrair
Figura 4: Subtraindo horas no PQ


Molezinha, né?! Agora basta transformarmos essa coluna usando esse recurso do Power Query:

Etapas:
1. Clique com o mouse na coluna Subtração
2. Vá em Transformar
3. Clique em Duração
4. Selecione Total de Horas
Figura 5: Duração no Power Query

Vamos renomear essa coluna Subtração para Horas. Você pode editar as etapas anteriores ou adicionar uma nova etapa. Sugiro sempre reduzir o número de etapas aplicadas editando as etapas anteriores para melhorar a performance e tornar o carregamento (e atualização) dos dados mais rápido.

Agora que já temos a coluna com a tempo decorrido (Horas), podemos dividir as colunas DataHoraInicial e DataHoraFinal em duas colunas cada (uma com data e outra com hora), certo?! Faremos isso usando o recurso Dividir Coluna do PQ.

Etapas:
1. Clique com o mouse na coluna DataHoraInicial
2. Vá em Transformar
3. Clique em Dividir Coluna
4. Selecione Por Delimitador
5. Selecione Espaço e Cada Ocorrência do Delimitador
Figura 6: Dividindo coluna

Você notará que o PBI ao dividir essa coluna em duas, automaticamente renomeou para DataHoraInicial.1 e DataHoraInicial.2.

Figura 7: Resultado da divisão da coluna DataHoraInicial

Ficaram feios esses nomes, concorda?! Vamos editar etapas anteriores para renomeá-las:

Figura 7: Editando etapas anteriores para renomear colunas

Veja acima que renomeamos DataHoraInicial.1 para DataInicial e DataHoraInicial.2 para HoraInicial.

Você deve repetir exatamente o mesmo procedimento com a coluna DataHoraFinal, ok?!

Por fim, clique em Fechar e Aplicar.


Medidas no formato hh:mm:ss

Criaremos nossa primeira medida! Antes disso, vamos inserir uma tabela ‘em branco’ apenas para organizarmos nossas medidas ali no painel Campos.

Figura 8: Organizador de medidas


Nossa primeira medida será a soma de Horas.

Soma Horas Decimal = SUM ( fProducao[Horas] )

Repare que ao adicionarmos um visual do tipo Tabela com a coluna Operador e a medida Soma Horas Decimal, teremos o seguinte:

Figura 9: Tabela Horas por Operador


Agora precisamos fazer alguns cálculos para encontrar quantas horas inteiras, minutos e segundos tem essa medida. Para transformar esse valor decimal de horas em minutos basta multiplicarmos a parte decimal inteira (após a vírgula) por 60. Para converter minutos para segundos, precisamos pegar a parte decimal do resultado anterior e multiplicar por 60 novamente, certo? Veja os cálculos que fizemos na calculadora para o valor da primeira linha da Tabela (operador ELVIS):

Figura 10: Conversão de horas em minutos e segundos

Faremos uma medida DAX para replicar esse cálculo. Veja que não precisaremos criar várias medidas porque utilizaremos variáveis! Segue o comando completo para facilitar sua vida:

Soma Hora (hh:mm:ss) =
VAR vHorasDecimal = [Soma Horas Decimal]
VAR vHoras = INT ( vHorasDecimal )
VAR vMinutosDecimal = 60 * ( vHorasDecimal – vHoras )
VAR vMinutos = INT ( vMinutosDecimal )
VAR vSegundos = ROUND ( 60 * ( vMinutosDecimal – vMinutos )0 )
VAR vResultado = vHoras & “:” & vMinutos & “:” & vSegundos
RETURN
    vResultado

Veja que conseguimos trazer de volta o formato hh:mm:ss via DAX:

Figura 11: Formato hh:mm:ss via DAX


Perceba que se tentarmos inserir essa medida no eixo de algum gráfico padrão do Power BI, não conseguiremos. Mas você pode usar essa medida como tooltip:

Figura 12: Formato hh:mm:ss em Tooltip

Como nosso total de horas está em decimal, será tranquilo calcular a média, veja:

Media Horas Decimal = AVERAGE ( fProducao[Horas] )


Para calcular a média no formato hh:mm:ss, basta você copiar o código da medida Soma horas (hh:mm:ss) e alterar a variável vHorasDecimal para utilizar a medida Média Horas Decimal no lugar de Soma Horas Decimal.

Vamos ver como ficaram essas duas novas medidas na tabela?

Figura 13: Médias

Vocês sabem que tenho T.O.C., né?! Preciso arrumar essa última medida que criamos para deixarmos com dois dígitos as horas, minutos e segundos que aparecem ali.

Media Hora (hh:mm:ss) =
VAR vHorasDecimal = [Media Horas Decimal]
VAR vHoras = INT ( vHorasDecimal )
VAR vMinutosDecimal = 60 * ( vHorasDecimal – vHoras )
VAR vMinutos  INT ( vMinutosDecimal )
VAR vSegundos = ROUND ( 60 * ( vMinutosDecimal – vMinutos )0 )
VAR vHH = IF ( LEN ( vHoras ) = 1“0” & vHorasvHoras )
VAR vMM = IF ( LEN ( vMinutos ) = 1“0” & vMinutosvMinutos )
VAR vSS = IF ( LEN ( vSegundos ) = 1“0” & vSegundosvSegundos )
VAR vResultado = vHH & “:” & vMM & “:” & vSS
RETURN
    vResultado


Vamos aproveitar e arrumar também a medida Soma Hora (hh:mm:ss):

Soma Hora (hh:mm:ss) =
VAR vHorasDecimal = [Soma Horas Decimal]
VAR vHoras = INT ( vHorasDecimal )
VAR vMinutosDecimal = 60 * ( vHorasDecimal – vHoras )
VAR vMinutos  INT ( vMinutosDecimal )
VAR vSegundos = ROUND ( 60 * ( vMinutosDecimal – vMinutos )0 )
VAR vHH = IF ( LEN ( vHoras ) = 1“0” & vHorasvHoras )
VAR vMM = IF ( LEN ( vMinutos ) = 1“0” & vMinutosvMinutos )
VAR vSS = IF ( LEN ( vSegundos ) = 1“0” & vSegundosvSegundos )
VAR vResultado = vHH & “:” & vMM & “:” & vSS
RETURN
    vResultado


Drill down em segundos

A próxima tarefa será criar uma dCalendario e conseguirmos chegar até o nível de segundos no drill down!

Tenha em mente que a tabela dCalendario SEMPRE deve estar na granularidade de DATA, nunca de hora!

Essa tarefa parece complexa mas não é! Para relacionar as horas da tabela fato você vai precisar criar uma tabela dimensão de hora (dHora).

A primeira coisa a fazer é construir nossa dCalendario. Faremos via DAX, veja:

dCalendario = CALENDAR ( DATE ( 20200101 )DATE ( 20201231 ) )

Com a dCalendario criada, precisaremos relacioná-la com nossa tabela fato. A escolha da coluna DataInicial ou DataFinal dependerá do seu modelo – o que você considerará mais importante. Escolhi DataInicial da tabela fProducao para relacioná-la a coluna Date (da tabela dCalendario), veja:

Figura 14: Relacionando a tabela dCalendario

Vamos precisar voltar no Power Query para criar a tabela dHora. Primeiro crie uma Consulta nula e depois vá em Editor Avançado para colar o código que vou disponibilizar logo abaixo:

Figura 15: Criando a tabela dHora via Power Query


Código M para copiar e colar no Editor Avançado:

let
  Lista = {0..86399},
  ConverteParaTabela = Table.FromList(
      Lista, 
      Splitter.SplitByNothing(), 
      null, 
      null, 
      ExtraValues.Error
    ),
  AlteraTipo = Table.TransformColumnTypes(ConverteParaTabela, {{“Column1”, Int64.Type}}),
  AlteraNomeCol = Table.RenameColumns(AlteraTipo, {{“Column1”, “Segundo”}}),
  ColDuracao = Table.AddColumn(
      AlteraNomeCol, 
      “Duração”, 
      each #duration(0, 0, 0, [Segundo]), 
      type duration
    ),
  ColHora = Table.AddColumn(
      ColDuracao, 
      “Hora”, 
      each #time(
          Duration.Hours([Duração]), 
          Duration.Minutes([Duração]), 
          Duration.Seconds([Duração])
        ), 
      type time
    ),
  RemoveCols = Table.RemoveColumns(ColHora, {“Duração”, “Segundo”}),
  ColHoraNum = Table.AddColumn(RemoveCols, “Hora Num”, each Time.Hour([Hora]), Int64.Type),
  ColMinutoNum = Table.AddColumn(ColHoraNum, “Minuto Num”, each Time.Minute([Hora]), Int64.Type),
  ColSegundoNum = Table.AddColumn(ColMinutoNum, “Segundo Num”, each Time.Second([Hora]), Int64.Type),
  ColAMPM = Table.AddColumn(
      ColSegundoNum, 
      “AMPM”, 
      each if [Hora Num] < 12 then “AM” else “PM”, 
      type text
    ),
  ColHoraAMPM = Table.AddColumn(
      ColAMPM, 
      “Hora AMPM”, 
      each 
        if [Hora Num] = 0 then 
          “12 AM”
        else if [Hora Num] >= 1 and [Hora Num] <= 11 then 
          Number.ToText([Hora Num]) & ” AM”
        else if [Hora Num] = 12 then 
          “12 PM”
        else if [Hora Num] >= 13 then 
          Number.ToText([Hora Num]  12) & ” PM”
        else 
          “Unknown”, 
      type text
    ),
  ColPeriodo = Table.AddColumn(
      ColHoraAMPM, 
      “Período”, 
      each 
        if [Hora Num] >= 0 and [Hora Num] <= 5 then 
          “12AM to 6AM”
        else if [Hora Num] >= 6 and [Hora Num] <= 11 then 
          “6AM to 12PM”
        else if [Hora Num] >= 12 and [Hora Num] <= 17 then 
          “12PM to 6PM”
        else if [Hora Num] >= 18 and [Hora Num] <= 23 then 
          “6PM to 12AM”
        else 
          “Unknown”, 
      type text
    ),
  ColPeriodoOrdem = Table.AddColumn(
      ColPeriodo, 
      “Período Ordem”, 
      each 
        if [Hora Num] >= 0 and [Hora Num] <= 5 then 
          1
        else if [Hora Num] >= 6 and [Hora Num] <= 11 then 
          2
        else if [Hora Num] >= 12 and [Hora Num] <= 17 then 
          3
        else if [Hora Num] >= 18 and [Hora Num] <= 23 then 
          4
        else 
          5, 
      Int64.Type
    )
in
  ColPeriodoOrdem

A referência original desse código pode ser encontrada clicando aqui.

Após renomear a Consulta1 para dHora, nossa tabela estará assim:

Figura 16: Tabela dHora

Perceba que teremos 1 nova linha para cada segundo da coluna Hora. Ou seja, se multiplicarmos 60 (seg) x 60 (min) x 24 (h) teremos o total de 86400 linhas, ok?!

Clique em Fechar e Aplicar e relacione a tabela dHora com a tabela fProducao:

Figura 17: Relacionando a tabela dHora com a tabela fato

Vamos ver como ficará o Drill down no gráfico de área ? Veja como fizemos para chegar até o nível AMPM:

Figura 18: Navegando pelos níveis do drill down até AMPM


Continuando até o detalhe em segundos:

Figura 19: Navegando pelos níveis do drill down até segundos


Dica Bônus

Durante a Live #27 eu tinha comentado ao vivo que NÃO tinha como adicionar o formato hh:mm:ss num gráfico sem ser via tooltip. Mas o nosso colega Alfonso Haskel nos salvou!

Ah, vou aproveitar e indicar esse vídeo que mostra como utilizar outros formatos de número personalizados no Power BI – como por exemplo aquele parênteses para números negativos que o pessoal de finanças e contábeis costuma usar.

Bom, vamos à solução! A primeira coisa que faremos é concatenar as horas, minutos e segundos e depois converter ir resultado para Inteiro usando a função CONVERT diretamente naquela medida Soma Hora (hh:mm:ss):

Soma Hora (hh:mm:ss) =
VAR vHorasDecimal = [Soma Horas Decimal]
VAR vHoras = INT ( vHorasDecimal )
VAR vMinutosDecimal = 60 * ( vHorasDecimal – vHoras )
VAR vMinutos = INT ( vMinutosDecimal )
VAR vSegundos = ROUND ( 60 * ( vMinutosDecimal – vMinutos )0 )
VAR vHH = IF ( LEN ( vHoras ) = 1“0” & vHorasvHoras )
VAR vMM =&nbspIF ( LEN ( vMinutos ) = 1“0” & vMinutosvMinutos )
VAR vSS = IF ( LEN ( vSegundos ) = 1“0” & vSegundosvSegundos )
RETURN
    CONVERT ( vHH & vMM & vSSINTEGER )

Agora, basta mudarmos o formato dessa medida digitando em Formato “00:00:00”, veja:

Figura 20: Mudando o formato da medida Soma Hora (hh:mm:ss)

Preparados para ver como ficará essa medida num gráfico?

Figura 21: Rótulo com hora no formato hh:mm:ss


Para que exiba corretamente o formato que indicamos basta ir em Exibir unidades (em Rótulos – Painel Visualizações) e selecionar Nenhum, beleza? Deve-se fazer o mesmo no Eixo Y:

Figura 22: Eixo com hora no formato hh:mm:ss


Bom, fechou agora!
Espero que tenha gostado do conteúdo.

Um abraço,
Leonardo.

Compartilhe este post:
Compartilhar no facebook
Compartilhar no linkedin
Compartilhar no twitter
Compartilhar no pinterest