Power BI DAX Time Intelligence

Aprender os conceitos é muito importante pra conseguir extrair o máximo do DAX, por conta disso, estou estudando mais a fundo a teoria e, como sempre, vou deixar aqui todas minhas anotações sobre o que ando estudando.

Highlights

• colunas calculadas são calculadas a nível de dados, enquanto que medidas são calculadas a nível de relatório, portanto, nada que seja feito no relatório irá alterar o resultado do que foi calculado nas colunas calculadas.
• colunas calculadas são contexto de linha, enquanto medidas são contexto de filtro
• todas medidas aplicam, implicitamente, calculate()
• todas medidas transformam row context em filter context

Time Intelligence

• funções DAX de time intel não criam datas, apenas andam pra frente ou para trás em datas já existentes na tabela de data
• funções DAX de time intel não se aplicam a células (rows), apenas a filtros (tabelas)
• funções de time intel sempre trabalham a nível de data
• funções de time intel removem os filtros existentes no contexto para conseguirem calcular seus resultados adequadamente (melhor explicado ao longo do texto) (new filters overwrite the old filters)
• funções de time intel funcionam apenas em datas regulares (ano, trimestre, mês, dia). Não funciona em granularidades irregulares, como semana, por ex.
• funções de time intel funcionam apenas em calendários regulares.

Funções de time intel vem do MDX, logo, são funções de manipulação de filtros (filter context, filter manipulation).

Importante entender que funções de time intel manipulam filtros, e todos filtros são tabelas, logo funções de time intel manipulam tabelas. Elas também não conseguem produzir novas datas, não é possível utilizar a função Dateadd() para criar uma data aleatória que não exista na sua tabela de data, portanto, para que Dateadd() funcione é preciso que, antes, essa data que você quer atingir já exista dentro da sua tabela de data. Logo, perceba que as funções de inteligência de tempo não vão criar uma data nova, mas sim “shiftar”, ir pra frente ou pra trás, dentro das suas datas já existentes na sua tabela de data.

No exemplo da imagem acima, a expressão DAX cria, com summarize(), uma tabela de duas colunas. Pense em Summarize() como um group by, criando uma tabela de duas colunas, trazendo resultados únicos desses dois campos. Na sequência utiliza addcolumns() para adicionar mais uma coluna, chamada LastYearSales, que é o resultado de uma expressão calculate() que retorna a soma de Sales Amount no mesmo período do ano anterior, usando sameperiodlastyear().

O objetivo com essa medida é trazer, pra cada registro da tabela criada, que foi nossa tabela de duas colunas, Year e Quarter, o valor de quanto foi vendido no ano anterior para cada linha dessa tabela.

Addcolumns() é uma função iteradora, ela vai linha a linha executando o código. Então a tabela é criada com summarize() no row context, e depois, quando é a hora de executar calculate() o contexto é transformado em contexto de filtro.

Quando sameperiodlastyear() é executado uma lista de datas será retornada de acordo com a tabela que temos, nesse caso uma lista de datas referente ao Quarter 2 de 2012, que vai de 01/04/2012 a 30/06/2012. Lembrando que esse ciclo de cálculo se repetirá linha a linha. Outro ponto importante é saber que funções de time intelligence sempre funcionam a nível de data, e mesmo que nesse caso a granularidade seja de Ano e Quarter, o retorno da função de time intelligence será uma lista de datas.

Uma vez que o filtro da função de time intel é adicionado ao contexto do dax, o filtro anterior é removido do contexto, isso porque nenhum registro, nenhuma linha, nenhum resultado sobreviveria a um filtro como esse, onde o ano é 2013 e 2012 ao mesmo tempo. Por causa disso, o contexto anterior é removido e o cálculo é feito com base na nova lista de datas. Depois disso é verificado quais linhas de Sales Amount sobrevivem a esse filtro, aquelas que forem selecionadas serão então agregadas, nesse caso em SUM(). Somente depois de agregada, será inserida de volta no contexto original.

Filtros e Time Intelligence

Uma das partes mais importantes é entender como as funções de time intel aplicam seus filtros. Isso porque esses filtros são os responsáveis por garantir o bom funcionamento, e o resultado de função de time intel. Por isso, saiba que:

• toda função de time intel aplica, implicitamente, um all() em toda tabela de data

Ou seja, toda vez que você chama uma função de time intel ela aplica implicitamente um All( Date ), e faz isso pra garantir que o filtro do contexto atual não atrapalhe na hora de conseguir a lista de datas necessárias para executar a função de time intel. Por isso, é importante sempre garantir que sua dimensão dCalendário, no caso desse post estamos chamando de Date, esteja sempre marcada como date-table, pois dessa forma, quando a função de time intel for aplicar o ALL() ela saberá qual tabela filtrar!

Vamos analisar a medida acima. Perceba que o argumento que passamos na sameperiodlastyear() é apenas Date[Date], ou seja, apenas a coluna de date. Sabendo que toda medida trabalha em filter context e que todo filtro é uma tabela, esse filtro então representa uma tabela de uma única coluna, Date[Date]. No entanto, no nosso exemplo não existe nenhum filtro sobre a coluna de Date[Date], na verdade, os filtros no contexto atual são sobre Year e Quarter. Portanto, se a função de time intel não aplicasse o All( Date ) ele jamais seria capaz de retornar a lista de datas que precisamos. No nosso contexto o ano é 2013 e o quarter 2, e precisamos pegar o sameperiodlastyear() desse período, porém, como nosso contexto aplica filtro sobre Year e Quarter, por mais que fosse passado um Calculate() com Filter em Date[Date] o filtro não seria desfeito, pois as colunas filtradas são Year e Quarter, o que impossibilitaria para o DAX retornar a lista de Datas que precisamos, que vai de 01/04/2012 a 30/06/2012. Para prevenir esse comportamento, toda função de time intel aplica All( Date ), dessa forma desfaz, remove o filtro do contexto inicial, abrindo toda tabela, dessa forma agora tem condições de encontrar a lista de datas que precisa.

Porém, para garantir que esse comportamento aconteça o DAX precisa de algumas condições, sendo:

• uma tabela de datas com datas únicas e com anos completos, de 01/01 a 31/12, sem gaps, sem falhas, na lista de datas
• a coluna de data deve ser do tipo date ou datetime
• a tabela de data deve ser, preferencialmente, marcada como date-table
• caso sua tabela de data não faça relacionamento usando a tabela de data e sim um id de data (campo numérico) sua tabela de data deve ser obrigatoriamente marcada como data-table, ou então não será possível realizar cálculos com funções de time intel
• caso sua tabela de data faça relacionamento usando o campo date, isso ajuda a indicar pra o DAX de que aquela é a tabela a ser utilizada pelas funções de time intel
• caso sua data contenha time (datetime) o valor do tempo deve ser sempre o mesmo entre todas linhas de date[date]

Caso você não tenha uma tabela marcada como date-table e o Power BI não consiga identificar através dos relacionamentos, ou de suas tabelas de datas escondidas (assunto pra outro post), o resultado da sua função de time intel será obrigatoriamente incorreto. Para contornar isso, garanta que você passe em sua medida um ALL( Date ), para ignorar quaisquer filtros na tabela de data e conseguir recuperar a lista de datas desejada.

Referências

https://youtu.be/8v4eqssS4a4 – Learn Dax Part II by Jeffrey Wang

https://dax.guide/sameperiodlastyear/

Conversa com Fred, o cara é fera mesmo =)