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.

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.

let
Fonte = List.Dates(#date(2014, 01, 01),1096, #duration(1,0,0,0)),
#"Convertido para Tabela" = Table.FromList(Fonte, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Colunas Renomeadas" = Table.RenameColumns(#"Convertido para Tabela",{{"Column1", "Data"}}),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Colunas Renomeadas",{{"Data", type date}}),
#"Ano Inserido" = Table.AddColumn(#"Tipo Alterado", "Ano", each Date.Year([Data]), Int64.Type),
#"Colunas Removidas" = Table.RemoveColumns(#"Ano Inserido",{"Ano"}),
#"Ano Inserido1" = Table.AddColumn(#"Colunas Removidas", "Ano", each Date.Year([Data]), Int64.Type),
#"Mês Inserido" = Table.AddColumn(#"Ano Inserido1", "Mês", each Date.Month([Data]), Int64.Type),
#"Nome do Mês Inserido" = Table.AddColumn(#"Mês Inserido", "Nome do Mês", each Date.MonthName([Data]), type text),
#"Trimestre Inserido" = Table.AddColumn(#"Nome do Mês Inserido", "Trimestre", each Date.QuarterOfYear([Data]), Int64.Type),
#"Dia Inserido" = Table.AddColumn(#"Trimestre Inserido", "Dia", each Date.Day([Data]), Int64.Type),
#"Dia da Semana Inserido" = Table.AddColumn(#"Dia Inserido", "Dia da Semana", each Date.DayOfWeek([Data]), Int64.Type),
#"Nome do Dia Inserido" = Table.AddColumn(#"Dia da Semana Inserido", "Nome do Dia", each Date.DayOfWeekName([Data]), type text),
#"Coluna Mesclada Inserida" = Table.AddColumn(#"Nome do Dia Inserido", "Mês-Ano", each Text.Combine({Text.From([Mês], "pt-BR"), Text.From([Ano], "pt-BR")}, "-"), type text)
in
#"Coluna Mesclada Inserida"

 

Criar Tabela Calendário Dinâmico em M

Neste exemplo vou precisar de uma tabela fato para buscar a data da primeira venda e a data da última venda.

Abaixo do artigo tem um link para baixar os arquivos utilizados no artigo. O nome da Tabela fato é tb_Vendas e o campo de datas é Data_venda.

• Criar a Função fnCalendario

Para começar crie uma nova consulta nula. Na guia Página Inicial –> Obter Dados –> Consulta Nula.

Vá para guia Exibição – Editor Avançado.

Cole o código abaixo dentro do Editor Avançado e clique em Concluído.

//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),
//Criando Colunas adicionais
//Coluna Ano
Ano = Table.AddColumn(ListaparaTabela, "Ano", 
each Date.Year([Data])),
//Criando Trimestre
Trimestre = Table.AddColumn(Ano , "Trimestre", 
each "T" & Number.ToText(Date.QuarterOfYear([Data]))),
//Número da Semana
NumeroSemana = Table.AddColumn(Trimestre , "Número Semana", 
each Date.WeekOfYear([Data])),
//Numero Mês
MesNumero = Table.AddColumn(NumeroSemana, "Número Mês", 
each Date.Month([Data])),
//Nome do Mes
NomeMes = Table.AddColumn(MesNumero , "Mês", 
each Date.ToText([Data],"MMMM")),
//Dia da Semana
DiaDaSemana = Table.AddColumn(NomeMes , "Dia da Semana", 
each Date.ToText([Data],"dddd")),
//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.