Criando tabela de datas para o Power BI e SSAS 2016

By | 25/04/2016

Olá pessoal,

Uma dúvida comum que recebo é sobre a criação de tabelas de datas no PowerBI, PowerPivot e SSAS Tabular, quando não temos uma na nossa fonte de dados.

A solução mais fácil neste cenário é através de DAX. O Power BI, o Analysis Services 2016 (e o PowerPivot do Excel 2016) trazem algumas funções bastante úteis para manipulação de datas. Além de oferecem a opção de Tabela Calculada (Calculated Table) onde é possível criar uma tabela no modelo utilizando apenas DAX.

image

Para esta tarefa podemos lançar mão das novas funções CALENDAR() e a CALENDARAUTO().

A função CALENDAR() recebe duas datas e retorna uma tabela com todas as datas entre as datas especificadas (intervalo fechado, isto é, inclui as datas passadas como parâmetros).

A função CALENDARAUTO() apresenta um resultado similar, porém não recebe parâmetros. São utilizadas a menor e a maior data presente no modelo e é criado um intervalo (anual) com estas datas. É possível especificar qual é o mês de término do ano (para casos de ano fiscal não terminar em dezembro), por padrão dezembro será utilizado.

Imagine que tenho a seguinte tabela no meu modelo (fiz bastante simplificado), onde o desejado é criar uma tabela de datas para suprir as datas em um intervalo que atenda estes dados.

image

Podemos resolver com CALENDAR():

=CALENDAR (DATE (2007, 9, 1), DATE (2007, 9, 31))

Lembrem-se que o uso de , ou ; dependerá da linguagem instalada. Em caso de dúvidas veja o que o intellisense está sugerindo.

image

E fazendo dinamicamente seria algo como a seguir, perceba que neste caso usamos uma tabela como referencia para data mínima e data máxima (não precisa ser a mesma), mas o problema é que pode existir outras tabelas com datas com valores maiores ou menores do que a tabela referenciada.

=CALENDAR (MINX (Sales, [Date]), MAXX (Forecast, [Date]))

Para acertar as datas de maneira dinâmica sem especificar o intervalo usamos  a CALENDARAUTO():

Para o CALENDARAUTO sem especificar parâmetros, como temos datas do ano de 2007, será gerado uma tabela com o intervalo de 01 de janeiro de 2007 até 31 de deembro de 2007.

image

Com este início de tabela criado o que deve ser feito é criar as demais colunas utilizando DAX (derivando da coluna de data gerada). Observe a imagem a seguir:

image

Para facilitar a vida dos leitores do blog, segue uma tabela resumo das funções DAX aplicadas, bem como a ordenação para que os cálculos e apresentação aconteçam como esperado. Assim é possível cada um otimizar para seu próprio uso. Lembrem-se: as tabelas e colunas são carregas sempre em memória. Só crie as colunas que realmente irá precisar/utilizar.

Nome da Coluna DAX Sort by Column

Year

=YEAR([Date])

 

MonthNo

=MONTH([Date])

 

QuarterNo

=INT(([MonthNo]+2)/3)

 

Quarter

=”Qtr “&[QuarterNo]

Sort by [QuarterNo]

Month

=FORMAT([Date],”MMMM”)

Sort by [MonthNo]

FiscalYear

=IF([MonthNo]<=[F],[Year],[Year]+1)

 

FiscalMonthNo

=IF(MOD([MonthNo]+[FiscalYearEndMonth],12)>0,
       MOD([MonthNo]+[FiscalYearEndMonth],12),12)

 

FiscalQuarterNo

=INT(([FiscalMonthNo]+2)/3)

 

FiscalQuarter

=”Qtr “&[FiscalQuarterNo]

 

FiscalMonth

=FORMAT([Date],”MMMM”)

Sort by [FiscalMonthNo]

FiscalYYYY-MM

=FORMAT([FiscalYear],”0000-“) & FORMAT([FiscalMonthNo],”00”)

 

MMM-YYYY

=FORMAT([Date],”MMM-YYYY”)

 

FiscalYYYY-QQ

=FORMAT([FiscalYear],”0000″)&”-Q”&FORMAT([FiscalQuarterNo],”0″)

 

DayOfWeekNo

=WEEKDAY([Date])

 

DayOfWeek

=FORMAT([Date],”DDDD”)

Sort by [DayOfWeekNo]

Day

=DAY([Date])

 

 

Por hoje era isso.
Abs, Zava

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios são marcados com *