O assunto hoje é projeção de valores com DAX.
Imagine que o seu chefe quer saber qual será o valor do faturamento daqui a 7 meses, como você faria esse cálculo?
Ferrou né?! Mas não se preocupe, fica comigo até o final que vou te ajudar a descascar esse abacaxi. O segredo está no entendimento dos contextos.
Clique Aqui para se tornar um expert em DAX aprendendo na prática diversas análises avançadas.
Base de Dados
A base de dados utilizada aqui neste exemplo, contém 367 linhas onde cada linha corresponde a uma venda/assinatura de um curso on-line.
Para complementar a base de dados, crie a dimensão calendário.
Clique em Modelagem > Nova Tabela, e cole o código abaixo.
dCalendario =VAR vDataMIN =
MIN ( fAssinaturas[Adesão] )
VAR vDataMAX =
DATE ( 2022, 12, 31 )
VAR vDataInicial =
DATE ( YEAR ( vDataMIN ), 01, 01 )
RETURN
CALENDAR ( vDataInicial, vDataMAX )
O trabalho com projeção de valores exige que a tabela de data contenha as datas futuras que serão utilizadas na projeção, aqui neste exemplo a data final é 31 dezembro de 2022.
Não se esqueça de ordenar a coluna nome do mês, utilizando a coluna mês numérica, OK?
Agora, relacione a tabela fato com a tabela calendário.
dCalendario [Data] –> fAssinaturas [Adesao] = é o relacionamento principal.
dCalendario [Data] –> fAssinaturas [Cancelamento] = é o relacionamento secundário, este deve ficar inativo para ser utilizado apenas quando necessário, sendo ativado via DAX.
Cálculos Iniciais
Antes de calcular as projeções, é necessário calcular o valor do faturamento.
Valor Assinatura =SUM ( fAssinaturas [Valor] )
O que será que aconteceu? Note que a data da tabela calendário está no formato data e a data de adesão da tabela fato está em formato data/hora, são formatos diferentes e por isso os valores ficaram indevidos.
Para corrigir isso, vá no Power Query e altere o tipo das datas da tabela fato, adesão e cancelamento de data/hora para data.
Caso ocorra algum erro na conversão, adicione nova etapa ao invés de substituir a atual.
Agora sim, as vendas estão sendo mostradas corretamente.
Mostrando Valor em Mês Futuro
Suponha que é necessário projetar o valor de julho $1377 no mês de Dezembro de 2021.
Para facilitar o entendimento, deixe na matriz apenas o ano de 2021.
Utilizando a medida abaixo, é possível obter este resultado.
Teste =VAR vMes = 7
VAR vAno = 2021
VAR vValor =
CALCULATE (
[Valor Assinatura],
ALL ( dCalendario ),
// Remova os filtros da tabela dCalendario
dCalendario[Mês Num] = vMes,
dCalendario[Ano] = vAno
)
RETURN
vValor
Perceba que agora o valor de julho foi replicado para todos os meses, inclusive dezembro que era o objetivo inicial.
Agora pense um pouco, o que pode ser feito para que esse valor seja replicado apenas no mês de dezembro? Usando o IF.
Primeiro inclua as variáveis na medida para encontrar os valores do AnoEixo e MesEixo, em seguida faça o IF.
Teste =VAR vMes = 7
VAR vAno = 2021
VAR vDataEixo =
MAX ( dCalendario[Data] )
VAR vAnoEixo =
YEAR ( vDataEixo )
VAR vMesEixo =
MONTH ( vDataEixo )
VAR vValor =
CALCULATE (
[Valor Assinatura],
ALL ( dCalendario ),
//Remove os filtros da tabela dCalendario
dCalendario[Mês Num] = vMes,
dCalendario[Ano] = vAno
)
RETURN
IF ( vAnoEixo = 2021 && vMesEixo = 12, vValor, BLANK () )
Veja que agora é mostrado o valor esperado, ou seja, o valor de julho foi replicado apenas no mês de dezembro de 2021
Pulo do Gato: para fazer uma boa projeção, é necessário que a regra de negócio esteja muito bem definida.
Forecast A Partir da Média
Imagine que agora necessidade é projetar valores com base na média do faturamento realizado no ano de 2021.
Ajuste a variável “vValor” utilizando o AVERAGEX para a média, e ALL para considerar tanto o mês nome que está sendo mostrado no visual, quanto o mês num que é está sendo utilizado para ordenar.
O return é apenas a variável vValor, sem IF.
Teste =VAR vMes = 7
VAR vAno = 2021
VAR vDataEixo =
MAX ( dCalendario[Data] )
VAR vAnoEixo =
YEAR ( vDataEixo )
VAR vMesEixo =
MONTH ( vDataEixo )
VAR vValor =
AVERAGEX ( ALL ( dCalendario[Mês], dCalendario[Mês Num] ), [Valor Assinatura] )
RETURN
vValor
Projetando a Média para Dez 2021 e Ano 2022
Agora sim, deve ser utilizado IF com uma combinação de E e OU para checar os contextos onde os valores devem ser mostrados.
Teste =VAR vMes = 7
VAR vAno = 2021
VAR vDataEixo =
MAX ( dCalendario[Data] )
VAR vAnoEixo =
YEAR ( vDataEixo )
VAR vMesEixo =
MONTH ( vDataEixo )
VAR vValor =
AVERAGEX (
ALL ( dCalendario[Mês], dCalendario[Mês Num], dCalendario[Ano] ),
[Valor Assinatura]
)
RETURN
IF ( ( vAnoEixo = 2021 && vMesEixo = 12 ) || vAnoEixo = 2022, vValor, BLANK () )
Antes de continuar, renomeie a medida “Teste” para “Forecast”, e renomeie a medida “Valor Assinatura” para “Realizado”.
Dica Legal: você pode deixar o cálculo mais dinâmico, descobrindo via DAX qual é o último mês com dados, ao invés de colocar o mês 12 de forma fixa como foi feito aqui.
Inclua na medida a variável “vUltimaDataFato” que irá checar qual é a última data com registro de venda.
Forecast =VAR vUltimaDataFato =
CALCULATE ( MAX ( fAssinaturas[adesão] ), ALL ( dCalendario ) )
VAR vDataEixo =
MIN ( dCalendario[Data] )
VAR vAnoEixo =
YEAR ( vDataEixo )
VAR vMesEixo =
MONTH ( vDataEixo )
VAR vValor =
AVERAGEX (
ALL ( dCalendario[Mês], dCalendario[Mês Num], dCalendario[Ano] ),
[Realizado]
)
RETURN
IF ( vDataEixo > vUltimaDataFato, vValor, BLANK () )
Trazendo Valor Total do Forecast
Note que o cartão que mostra o total de forecast está em branco, como isso pode ser resolvido?
Primeiro é preciso entender que isso está ocorrendo porque todo o cálculo foi feito considerando apenas o contexto do mês, dessa forma no contexto total não funciona.
Nesse caso utilize o SUMX para corrigir o contexto dos totais, conforme mostrado na medida abaixo.
Forecast Total =SUMX (
SUMMARIZE ( dCalendario, dCalendario[Ano], dCalendario[Mês Num] ),
[Forecast]
)
Forecast Considerando Média 3 Meses
Caso a necessidade do negócio seja projetar valores com base na média dos últimos 3 meses, adapte sua métrica para os meses sejam filtrados antes de calcular a média. Veja abaixo adaptação na variável “vValor”.
Forecast =// Pegando Nov / 2021 como exemplo
VAR vUltimaDataFato =
CALCULATE ( MAX ( fAssinaturas[adesão] ), ALL ( dCalendario ) ) // 09/11/2021
VAR vUltimoMes =
MONTH ( vUltimaDataFato )
VAR vDataEixo =
MIN ( dCalendario[Data] ) // 01/11/2021
VAR vAnoEixo =
YEAR ( vDataEixo )
VAR vMesEixo =
MONTH ( vDataEixo )
VAR vValor =
AVERAGEX (
FILTER (
ALL ( dCalendario[Mês], dCalendario[Mês Num], dCalendario[Ano] ),
dCalendario[Mês Num] >= vUltimoMes – 2
),
[Realizado]
)
RETURN
IF ( vDataEixo > vUltimaDataFato, vValor, BLANK () )
Entretanto, a melhor forma de fazer cálculos com meses anteriores é tendo na dimensão calendário um contador contínuo de meses.
Aqui está a lógica a ser aplicada.
E aqui está a medida para você copiar e colar aí na sua dimensão calendário.
Mês Contador =( dCalendario[Ano] – MIN ( dCalendario[Ano] ) ) * 12 + dCalendario[Mês Num]
Agora é só adaptar a medida levando em consideração a nova variável “vUltimoMesContador”.
Forecast =// Pegando Nov / 2021 como exemplo
VAR vUltimaDataFato =
CALCULATE ( MAX ( fAssinaturas[adesão] ), ALL ( dCalendario ) ) // 09/11/2021
VAR vUltimoMesContador =
LOOKUPVALUE ( dCalendario[Mês Contador], dCalendario[Data], vUltimaDataFato )
VAR vDataEixo =
MIN ( dCalendario[Data] ) // 01/11/2021
VAR vAnoEixo =
YEAR ( vDataEixo )
VAR vMesEixo =
MONTH ( vDataEixo )
VAR vValor =
AVERAGEX (
FILTER (
ALL ( dCalendario[Mês], dCalendario[Mês Num], dCalendario[Ano] ),
dCalendario[Mês Num] >= vUltimoMesContador – 2
),
[Realizado]
)
RETURN
IF ( vDataEixo > vUltimaDataFato, vValor, BLANK () )
Forecast da Média de 3 Meses + 15%
Perceba que, se você entender os contextos você poderá ir adaptando as métricas conforme a necessidade do negócio.
Aqui vai um último exemplo para fecharmos com chave de ouro.
O objetivo é fazer a média dos meses de agosto, setembro e outubro e adicionar 15%.
A métrica abaixo foi ajustada para filtrar os meses na coluna mês contador e em seguida é adicionado no valor final 15%.
Forecast =// Pegando Nov / 2021 como exemplo
VAR vUltimaDataFato =
CALCULATE ( MAX ( fAssinaturas[adesão] ), ALL ( dCalendario ) ) // 09/11/2021
VAR vUltimoMesContador =
LOOKUPVALUE ( dCalendario[Mês Contador], dCalendario[Data], vUltimaDataFato )
VAR vDataEixo =
MIN ( dCalendario[Data] ) // 01/11/2021
VAR vAnoEixo =
YEAR ( vDataEixo )
VAR vMesEixo =
MONTH ( vDataEixo )
VAR vValor =
AVERAGEX (
FILTER (
ALL (
dCalendario[Mês],
dCalendario[Mês Num],
dCalendario[Ano],
dCalendario[Mês Contador]
),
dCalendario[Mês Contador] >= vUltimoMesContador – 3
&& dCalendario[Mês Contador] < vUltimoMesContador
),
[Realizado] * 1.15
)
RETURN
IF ( vDataEixo > vUltimaDataFato, vValor, BLANK () )
E por hoje é isso galera, espero que você consiga replicar toda essa lógica em seus projetos quando houver necessidade, beleza!
PS: Para não perder o conteúdo completo da próxima live , entre no nosso canal do Telegram! Aviso tudo por lá!
Abraços, Léo
