Criar Tabela Calendário em M no Power BI – Estático e Dinâmico

A Tabela Calendário é umas das tabelas mais importantes para podermos criar  Medidas no DAX para análises de tempo no Power BI. A Tabela Calendário é fundamental para usar filtros e criar medidas de inteligência de tempo. Neste artigo vou mostrar como criar a Tabela Calendário no Power Query (Editor de Consultas) que utiliza a linguagem M.

Temos várias formas de criar a tabela calendário no Power BI. Podes ainda criar de forma dinâmica ou com datas fixas. Neste artigo também vou mostrar estas duas formas.

Tabela Calendário Fixo no Editor de Consultas (Linguagem M)

Dentro da linguagem M podemos criar uma tabela calendário dinâmico e outro estático. Vamos começar pelo mais fácil que é o estático.

• Crie uma Consulta em Branco.

Na guia Página Inicial –> Obter Dados –> Consulta Nula.

Irá abrir o Editor de Consultas. Agora na Barra de Fórmulas digite a fórmula abaixo e depois ENTER.

= List.Dates(#date(2013, 01, 01),1096, #duration(1,0,0,0))

Explicando:

A função List.Dates retorna uma lista de datas começando com o primeiro parâmetro que é #date(2013, 01, 01). ou seja irá começar do dia 01/01/2013. Esta data você pode alterar conforme sua necessidade.

O segundo parâmetro é a contagem do incremento. Neste exemplo quero que ele conte 1096 dias a partir do dia 01/01/2013. No terceiro parâmetro definimos qual o incremento que no exemplo é de dia em dia.

Feito isso ele irá criar uma lista do dia 01/01/2013 a 31/12/2017.

• Transformar a Lista em Tabela.

Agora na guia Transformar, clique em Para a Tabela.

• Renomear a Coluna para Data

Clique com o botão direito do mouse no cabeçalho da coluna e clique em Renomear.

• Alterar o Tipo para Data

Agora clique no ícone ao lado do nome Data que foi renomeado e escolha Data.

• Adicionar Colunas Personalizadas

Vamos criar as colunas de Dia, Mês, Ano, Semana, Nome do Mês.

Criar Coluna Ano – Clique na Guia Adicionar Colunas, no ícone Data no grupo Data e Hora e clique Ano – Ano.

Criar Coluna Mês – Clique na coluna Data e repita o passo acima e selecione Mês.

Agora repita para Nome do Mês, Trimestre, Dia, Dia da Semana, Nome do Dia.

Criar Coluna Mês-Ano – Selecione a tabela Mês e depois segurando a tecla CTRL selecione a coluna Ano. Vá na guia Adicionar Colunas e clique em Mesclar Colunas.

Na Janela que abrir selecione o separador como Personalizado no campo abaixo digite o sinal de – e no nome da coluna insira Mês-Ano e clique em OK.

Pode renomear a Consulta para o nome dCalendario.

Pronto, nossa tabela Calendário está criado. Caso não queira fazer todo esse passo a passo você pode copiar o código abaixo e colar em seu Editor Avançado. Veja como fazer.

Criar Tabela Calendário Dinâmico no Power BI

Copie o código abaixo. Em seu Power BI, Crie uma Consulta em Branco, no Editor de Consulta, vá pra guia Exibição e Clique Editor Avançado. Delete tudo que estive dentro da janela o cole o código abaixo. Clique em concluído e depois é só relacionar as datas com a tabela fato.

//DI = Data Início
//DF = Data Fim

let dCalendario =(DI as date, DF as date) as table =>

let
//Contar número de dias entre a data de início e fim
 Dias = Duration.Days(DF - DI) +1,
//Criando uma lista de datas
 Datas = List.Dates(DI, Dias, 
 #duration(1,0,0,0)),
//Converter Lista em Tabela
 ListaparaTabela = Table.FromList(Datas, 
 Splitter.SplitByNothing(), {"Data"}, null, ExtraValues.Error ),
AlterarTipo = Table.TransformColumnTypes(ListaparaTabela,{{"Data", type date}}),
//Criando Colunas adicionais
 //Coluna Ano
 Ano = Table.AddColumn(AlterarTipo, "Ano", 
 each Date.Year([Data]), Int64.Type),
//Criando Trimestre
 Trimestre = Table.AddColumn(Ano , "Trimestre", 
 each "Trim " & Number.ToText(Date.QuarterOfYear([Data])), type text),
//Número da Semana
 NumeroSemana = Table.AddColumn(Trimestre , "Número Semana", 
 each Date.WeekOfYear([Data]), Int64.Type),
//Numero Mês
 MesNumero = Table.AddColumn(NumeroSemana, "Número Mês", 
 each Date.Month([Data]), Int64.Type),
 DataINT = Table.AddColumn(MesNumero, "DateInt", each [Ano]*100 + [Número Mês], Int64.Type),
//Nome do Mes
 NomeMes = Table.AddColumn(DataINT , "Mês", 
 each Date.ToText([Data],"MMM"), type text),
 MesMaiusculo = Table.TransformColumns(NomeMes,{{"Mês", Text.Proper, type text}}),
//Dia da Semana
 DiaDaSemana = Table.AddColumn(MesMaiusculo , "Dia da Semana", 
 each Date.ToText([Data],"dddd"), type text),
//Mês-Ano
 #"MesAno" = Table.AddColumn(DiaDaSemana, "Mês Ano", each Text.Combine({Text.From([Número Mês], "pt-BR"), Text.From([Ano], "pt-BR")}, "-"), type text)


in
 MesAno

in dCalendario

Renomeie a Consulta para fnCalendario.

Feito isso foi criado uma Função com o nome fnCalendario.

• Criando a tabela Calendario

Crie uma nova consulta em branco o cole o código abaixo no Editor Avançado.

let
Source = fnCalendario(List.Min(tb_Vendas[Data_venda]), List.Max(tb_Vendas[Data_venda]))
in
 Source

Lembrando que tb_Vendas é a tabela Fato e Data_venda é o nome da coluna de datas.

Feito isso é criado a Tabela dCalendario Dinâmico. Todas vez que a tabela fato é atualizado a tabela calendário é atualizado automaticamente.

Clique Aqui para baixar o arquivo do artigo.

Assista Também o vídeo de como criar a Tabela Calendário no Youtube com M e DAX.