Tabela Calendário no Power BI

Tabela Calendário no Power BI.

Vamos falar mais uma vez sobre como fazer uma tabela d_calendário no Power BI? E como criar no Power Query.

Dessa vez, passarei o passo a passo com origem na tabela Fato!

Na publicação anterior a criação da d_calendário não está vinculada na tabela Fato, é algo manual e sempre que você precisar trabalhar em outro projeto terá que adaptar as datas iniciais e finais.

Neste passo-a-passo você conseguirá adaptar aos projetos sem se preocupar tanto com as datas iniciais e finais da Fato.

Bora lá fazer essa belezura.

Não me preocupei em colocar os trimestres, semestres e outros detalhes que dependendo do projeto podem precisar.

Vamos pegar a data inicial:

DataMin = List.Min(Fato[data])

Na sequência selecionamos a data final, lembrando que será atualizado automaticamente:

DataMax = List.Max(Fato[data])

E agora com as duas datas, conseguimos calcular a quantidade de dias no período:

QtdeDias = Duration.Days(DataMax - DataMin) + 1

É necessário colocar o ” + 1 ” para que considere todos os dias do período, caso esqueça, o último dia (mais atual) ficará de fora, faça o teste para ver… rsrsrs

No início sofria com isso, o “(Em branco)” sempre aparecia nos filtros do calendário.

Antes de fazer a lista com as datas eu “guardo” o ano da data inicial. Faço isso porque, depois de quebrar muito a cabeça, consegui encontrar uma maneira de deixar a criação do índice da coluna “mês/ano” automática. O mestre Leonardo Karpinski ensina em um vídeo como fazer isso, eu apenas deixei de uma forma que não precisa digitar o ano inicial:

AnoMin = Date.Year(DataMin)

Você entenderá mais pra frente o por quê desse “AnoMin”.

Já com todos os valores necessários para criar a lista de datas, bora começar a ver a d_calendario:

ListaDatas = List.Dates(DataMin, QtdeDias, #duration(1,0,0,0))

Lembrando que estou trabalhando com DIAS, caso precise trabalhar com horas, minutos e segundos você precisará utilizar o seguinte:

ListaDatas2 = List.DateTimes(DataMin, QtdeDias, #duration(0,0,1,0))

Preste atenção na função #duration, ela considera dias, horas, minutos e segundos, nessa sequência.

No ListaDatas faço a divisão entre dias, duração de 1 dia, já no ListaDatas2 a duração está por minutos, assim terá uma precisão maior. Dependendo do projeto você pode precisar desse ou até com divisão de segundos.

Depois de criar a lista é preciso transformar ela em tabela:

Tabela = Table.FromList(ListaDatas, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

Após ter a tabela, você fica à vontade de trabalhar conforme está acostumado, aqui passarei alguns passos: (transformar a coluna em data, adicionar coluna ano e coluna mês)

Tipo_Data = Table.TransformColumnTypes(Tabela,{{"Column1", type date}})
#"Colunas Renomeadas" = Table.RenameColumns(Tipo_Data,{{"Column1", "Data"}})
Ano = Table.AddColumn(#"Colunas Renomeadas", "ano", each Date.Year([Data]), Int64.Type)
Mes = Table.AddColumn(Ano, "mes", each Date.Month([Data]), Int64.Type)

Na sequência crio a coluna com a descrição do mês:

Descricao_Mes = Table.AddColumn(Mes, "nome_mes", each Text.Proper(Text.Start(Date.MonthName([Data]),3)), type text)

Eu gosto de trabalhar com o nome do mês abreviado e com a primeira letra maiúscula, para isso resumo alguns passos em uma linha.

Detalhando o código:

“Text.Proper” coloca a primeira letra em maiúsculo;
“Text.Start” seleciona a quantidade de caracteres que desejo, nesse caso são 3; e por fim
“Date.MonthName” pega o nome do mês da coluna Data.

São três etapas do Power Query resumidas em uma.

Com a descrição do mês disponível, crio a coluna “mês/ano”, só que aqui tem uma pegadinha, porque o mês é TEXTO e ano é NÚMERO, para isso utilizo o “Text.From” e informo que essa coluna é tipo TEXTO, conforme código abaixo:

Descricao_Mes_Ano = Table.AddColumn(Descricao_Mes, "mes_ano", each [nome_mes]&"/"&Text.From([ano]))
 Tipo_Mes_Ano = Table.TransformColumnTypes(Descricao_Mes_Ano,{{"mes_ano", type text}})

Aí surge um problema, como classificar a coluna “mês/ano” já que é texto?

Com a ajuda o Karpinski que resolvi meu problema, só que mesmo assim dependia de um insert manual nessa etapa do ano inicial, foi por causa dessa dependência que busquei alternativas e cheguei nesse código para criar o índice e já aproveito para classificar como número inteiro:

Indice_Mes_Ano = Table.AddColumn(Tipo_Mes_Ano, "indice_mes_ano", each ([ano]-AnoMin)*12+[mes])
 Tipo_Indice_Mes_Ano = Table.TransformColumnTypes(Indice_Mes_Ano,{{"indice_mes_ano", Int64.Type}})

Lembra do “AnoMin”, apareceu aqui, com ele o código fica mais limpo e o que precisa ser mudado são somente as duas primeiras linhas onde as referências são as datas da tabela Fato.

Para finalizar, segue código completo:

let
    DataMin = List.Min(Fato[data]),
    DataMax = List.Max(Fato[data]),
    QtdeDias = Duration.Days(DataMax - DataMin) +1,
    AnoMin = Date.Year(DataMin),
    ListaDatas = List.Dates(DataMin, QtdeDias, #duration(1,0,0,0)),
    Tabela = Table.FromList(ListaDatas, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Tipo_Data = Table.TransformColumnTypes(Tabela,{{"Column1", type date}}),
    #"Colunas Renomeadas" = Table.RenameColumns(Tipo_Data,{{"Column1", "Data"}}),
    Ano = Table.AddColumn(#"Colunas Renomeadas", "ano", each Date.Year([Data]), Int64.Type),
    Mes = Table.AddColumn(Ano, "mes", each Date.Month([Data]), Int64.Type),
    Descricao_Mes = Table.AddColumn(Mes, "nome_mes", each Text.Proper(Text.Start(Date.MonthName([Data]),3)), type text),
    Descricao_Mes_Ano = Table.AddColumn(Descricao_Mes, "mes_ano", each [nome_mes]&"/"&Text.From([ano])),
    Tipo_Mes_Ano = Table.TransformColumnTypes(Descricao_Mes_Ano,{{"mes_ano", type text}}),
    Indice_Mes_Ano = Table.AddColumn(Tipo_Mes_Ano, "indice_mes_ano", each ([ano]-AnoMin)*12+[mes]),
    Tipo_Indice_Mes_Ano = Table.TransformColumnTypes(Indice_Mes_Ano,{{"indice_mes_ano", Int64.Type}})
in
    
    Tipo_Indice_Mes_Ano

rafaellemospentaho rafaellemospbi