Blog Power BI Experience

Projeção de Valores com DAX

Leonardo Karpinski
Leonardo Karpinski

Founder da Power BI Experience

Facebook
Twitter
LinkedIn

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.

Figura 16 – Base de Dados

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 ( 20221231 )
VAR vDataInicial =
    DATE ( YEAR ( vDataMIN )0101 )
RETURN
    CALENDAR ( vDataInicialvDataMAX )

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?

Figura 01 – Ordernando_Meses

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.

Figura 02 – Relacionamentos

Cálculos Iniciais

Antes de calcular as projeções, é necessário calcular o valor do faturamento.

Valor Assinatura =
SUM  (  fAssinaturas [Valor]  )

Figura 03 – Checagem de Valores

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.

Figura 04 – Formato Data

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.

Figura 05 – Altera Tipo de Dado

Agora sim, as vendas estão sendo mostradas corretamente.

Figura 06 – Vendas

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.

Figura 07 – Valor Analisado

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.

Figura 08 – Valor Replicado

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 = 12vValorBLANK () )

Veja que agora é mostrado o valor esperado, ou seja, o valor de julho foi replicado apenas no mês de dezembro de 2021

Figura 09 – Valor Replicado em Dezembro

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

Figura 10 – Resultado Valor Médio

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 = 2022vValorBLANK () )

Figura 11 – Resultado Projeção 2022

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 > vUltimaDataFatovValorBLANK () )

Trazendo Valor Total do Forecast

Note que o cartão que mostra o total de forecast está em branco, como isso pode ser resolvido?

Figura 12 – Card Forecast

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]
)

Figura 13 – Resultado Forecast Total

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 > vUltimaDataFatovValorBLANK () )

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.

Figura 14 – Lógica Contador Contínuo

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 > vUltimaDataFatovValorBLANK () )

Figura 15 – Resultado com Regra dos 3 Meses

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 > vUltimaDataFatovValorBLANK () )

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