POWER BI – Criação de um calendário DAX

POWER BI – Criação de um calendário DAX

Existem muitos casos em que você precisará criar uma tabela de datas no desktop do Power BI. Isso pode ser tão simples como criar uma tabela de data mestre ou mais complexo, como criar um número de índice mensal ou semanal vinculado a uma data. Para criar uma tabela de datas, existem dois métodos para criar uma tabela de datas. O método um, cria a tabela diretamente no Power BI Desktop, ou o método dois carrega a tabela de datas da fonte de dados.

Para este tutorial, examinaremos alguns exemplos diferentes que abordam especificamente a criação de um calendário de datas por meio de expressões DAX.

Vamos começar fazendo uma tabela básica. Abra o Power BI Desktop, na  faixa de opções Modelagem, clique em  Nova Tabela .

Na barra de fórmulas, insira a seguinte expressão DAX:

Dates  = 
  GENERATE ( 
    CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) ), 
    VAR currentDay = [Date]
    VAR day = DAY( currentDay )
    VAR month =  MONTH ( currentDay ) 
    VAR year =  YEAR ( currentDay )
  RETURN   ROW ( 
    "day", day, 
    "month", month, 
    "year", year )
  )

Isso gera uma tabela de data simples. Vamos examinar o que está acontecendo aqui.

1. A função CALENDAR DAX gera uma tabela com uma lista de datas de 1º de janeiro a 31 de dezembro de 2017.
2. Definimos variáveis ​​(denotadas por VAR) para capturar detalhes da coluna chamada [Data] que é criada pela função CALENDÁRIO.
3. A função Return gera uma linha por vez. A linha itera em cada item [Data] na lista criada pela função CALENDÁRIO. As variáveis ​​são recalculadas para cada execução de linha.

Nota: Ao criar tabelas DAX como estamos fazendo neste exemplo, a tabela DAX é atualizada apenas quando o relatório é atualizado. Portanto, se você quiser que a lista de datas aumente com o tempo, ou usar um NOW () na tabela DAX, você precisará agendar atualizações para o relatório do Power BI no serviço PowerBI.com.

Por outro lado, também podemos gerar a mesma tabela de dados calculando nossos dados coluna por coluna. Novamente, na  faixa de opções Modeling, clique no  ícone New Table e adicione o seguinte DAX:

Dates 2 = ADDCOLUMNS(
  CALENDAR( DATE( 2017, 1, 1) , DATE(2017, 12, 31) ), 
  "day", DAY([Date]), 
  "month", MONTH([Date]), 
  "year", YEAR([Date])
  )

Embora isso seja ótimo, temos uma tabela de datas agora, mas o que falta é flexibilidade e inteligência de tempo automática. Uma opção para alterar esta tabela para detectar automaticamente as datas em seu modelo de dados é substituir a instrução CALENDAR DAX por CALENDARAUTO ().

Para usar CALENDARAUTO, precisamos fornecer uma tabela com uma coluna de datas. Criaremos rapidamente uma tabela de dados fictícia com algumas datas, para que possamos usar CALENDARAUTIO.

Clique em  Inserir dados na  faixa inicial . Insira as seguintes informações na tela Criar Tabela. Clique em  Carregar para adicionar esses dados ao modelo de dados.

Agora que carregamos uma tabela no modelo com duas datas, podemos adicionar nossa nova tabela de datas. Na  faixa de opções Modeling, clique no  ícone New Table e adicione o seguinte DAX:

Dates 3 =
  GENERATE (
    CALENDARAUTO(),
    VAR currentDay = [Date]
    VAR day = DAY( currentDay )
    VAR month =  MONTH ( currentDay )
    VAR year =  YEAR ( currentDay )
  RETURN  ROW ( 
    "day", day,
    "month", month,
    "year", year )
  )

Nota: Na tabela MyData adicionamos duas datas, 03/03/2017 e 30/10/2017. Quando olhamos para as datas incluídas na nova tabela da Data 3, temos todas as datas listadas de 1º de janeiro a 31 de dezembro. Isso ocorre porque a função DAX CALENDARAUTO retornará todo o ano das datas do calendário, mesmo se encontrar apenas uma data em um determinado período de ano.  

Digamos que desejamos construir um calendário de datas que crescerá e mudará automaticamente com o tempo. Queremos identificar a data de hoje e, em seguida, criar uma lista de datas para o ano anterior.

Voltando à geração de uma tabela de datas por linhas, agora podemos usar a função DAX NOW. Na  faixa de opções Modeling, clique no  ícone New Table e adicione o seguinte DAX:

Dates 4  =
  GENERATE (
    CALENDAR( DATE( YEAR( NOW() ) - 1, MONTH( NOW() ), DAY( NOW()) ), NOW()),
    VAR currentDay = [Date]
    VAR day = DAY( currentDay )
    VAR month = MONTH ( currentDay )
    VAR year = YEAR ( currentDay )
  RETURN ROW (
    "day", day,
    "month", month,
    "year", year )
 )

Nota: nesta tabela DAX, usamos a função NOW () que retorna uma data e hora. O mesmo pode ser feito ao usar a função TODAY () que retorna apenas a data e não a hora.

Este agora gera uma tabela de datas que começa há um ano e preenche todas as datas até hoje. Por exemplo, se hoje for 29-10-2017, a lista de datas começaria em 29-10-2016 e terminaria em 29-10-2017. Muito legal…

Vamos continuar descendo a toca do coelho. Também podemos começar a adicionar cálculos que nos ajudam a percorrer os cálculos de data e hora. Por exemplo, você pode desejar calcular as vendas totais deste mês e possivelmente as vendas do mês anterior. Ao adicionar colunas com um índice, você pode mudar rapidamente os períodos de tempo. Isso torna os cálculos de tempo muito mais fáceis.

Na  faixa de opções Modeling, clique no  ícone New Table e adicione o seguinte DAX:

Datas 5 = 
  GERAR ( 
    CALENDÁRIO (DATA (ANO (HOJE ()) - 2, MÊS (HOJE ()), DIA (HOJE ())), HOJE ()), 
    VAR startOfWeek = 1 // Onde 1 é Domingo e 7 é sábado, portanto, 3 seria terça-feira     
    VAR dia atual = [data] 
    VAR dias = DIA (dia atual) 
    VAR meses = MÊS (dia atual) 
    VAR anos = ANO (dia atual) 
    VAR agoraAno = ANO (HOJE ()) 
    VAR agoraMês = MÊS ( TODAY ()) 
    VAR dayIndex = DATEDIFF (currentDay, TODAY (), DAY) * -1 
    VAR todayNum = WEEKDAY (TODAY ()) 
    VAR weekIndex = INT (ROUNDDOWN ((dayIndex + -1 * IF (todayNum + startOfWeek <= 6 , todayNum + startOfWeek, todayNum + startOfWeek - 7)) / 7, 0)) 
  RETORNAR LINHA (
    "dia", dias, 
    "mês", meses, 
    "ano", anos, 
    "índice do dia", índice do dia, 
 Dates 5 =
  GENERATE (
    CALENDAR( DATE( YEAR( TODAY() ) - 2, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),
    VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday    
    VAR currentDay = [Date]
    VAR days = DAY( currentDay )
    VAR months = MONTH ( currentDay )
    VAR years = YEAR ( currentDay )
    VAR nowYear = YEAR( TODAY() )
    VAR nowMonth = MONTH( TODAY() )
    VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
    VAR todayNum = WEEKDAY( TODAY() )
    VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
  RETURN ROW (
    "day", days,
    "month", months,
    "year", years,
    "day index", dayIndex,
    "week index", weekIndex,
    "month index", INT( (years - nowYear ) * 12 + months - nowMonth ),
    "year index", INT( years - nowYear )
  )
)

Observação: a equação DAX acima funcionará em seu relatório sem nenhuma alteração. No entanto, criei uma variável chamada  startOfWeek . Esta variável permite definir o dia de início da semana. Por exemplo, se seus dados começarem uma nova semana no domingo, o número startOfWeek será 1. Se os dados do início da semana começarem na quarta-feira, o número do início da semana será 4. Isso permite que você detecte automaticamente o dia da semana e, em seguida, organiza automaticamente todos os seus números de índice semanais no formato correto. Experimente brincar com essa variável para ver como a tabela DAX muda.

Então, por que trabalhar tanto na mesa de datas? Bem, por ter uma tabela de datas robusta, você pode simplificar muitas das medidas que precisa construir para o seu relatório. Considere o seguinte exemplo:

Você tem uma tabela de vendas com uma coluna de data e vendas.

E você tem nossa sofisticada Tabela de Datas 5 que criamos anteriormente:

A tabela Date 5 está vinculada à tabela Sample Sales:

Agora você pode construir as seguintes medidas DAX dentro da tabela Sample Sales:

Total Sales = SUM( 'Sample Sales'[Sales] )

e

Last Week Sales = CALCULATE( [Total Sales],  ALL('Dates 5'),  'Dates 5'[week index] = -1 )

Se você deseja calcular algo maluco como as últimas 5 semanas de vendas, pode calcular o seguinte:

Last 5 Weeks Sales = CALCULATE( [Total Sales], ALL( 'Dates 5' ),  AND( 'Dates 5'[week index]  <= -1,  'Dates 5'[week index] >= -5 ) )

O bom dessas medidas é que, sempre que o conjunto de dados for atualizado, as datas recalcularão automaticamente a última semana e as últimas cinco semanas.

Se você deseja ser capaz de lidar com o contexto de filtro adicional do visual, pode selecionar o contexto de filtro visual usando variáveis ​​(VAR). Então você pode RETORNAR uma função de cálculo que mudará todos os seus intervalos de tempo para você.

Moving Last Week Sales =  VAR filterTime = SELECTEDVALUE('Dates 5'[week index], BLANK()) RETURN CALCULATE( [Total Sales],  ALL( 'Dates 5'[Date] ), 'Dates 5'[week index] = filterTime - 1 )

O mesmo vale para uma soma móvel das últimas cinco semanas de vendas.

Moving Last 5 Weeks Sales =  VAR filterTime = SELECTEDVALUE('Dates 5'[week index], BLANK()) RETURN CALCULATE([Total Sales], ALL('Dates 5'[Date]), AND( 'Dates 5'[week index] <= filterTime -1, 'Dates 5'[week index] >= filterTime -5 ) )

Bem, isso é sobre ele. Obrigado por acompanhar.

Estou muito grato por você ter dedicado tempo para ler meu tutorial. Minha esperança é que, ao usar esses tutoriais gratuitos, você se torne uma estrela do rock no trabalho.

Créditos: https://powerbi.tips/2017/11/creating-a-dax-calendar/