Fala, pessoal!
Você já precisou realizar comparações anuais ou mensais de faturamento, quantidade de clientes, produtos, etc ? Calcular alguns desses valores de forma acumulada ao longo do ano? Ou calcular qualquer valor que dependa de alguma variável temporal (mês, ano, dia)? Então você não pode deixar de ver o nosso conteúdo de hoje porque falaremos sobre funções DAX de Inteligência de Tempo. Há diversas funções prontas para facilitar as agregações ao longo do tempo e você verá como é prático e fácil utilizá-las! E se você quiser se aprofundar nesse assunto se inscreva no meu curso clicando aqui: DAX Hands On. Nele você aprenderá diversas análises avançadas na prática.
Antes de começar, indicarei essas duas fontes de consulta para funções dax: Documentação oficial do Power BI, e Dax.Guide.
Descrição do Cenário
Temos um cenário com as seguintes tabelas: dCalendario, dCliente, dProduto, fMetas, fVendas.
No conteúdo de hoje focaremos na Linguagem DAX, então a parte de importação, transformação e carregamento de dados ficará para um outro dia, beleza?
Vamos imaginar que já temos a medida Total Vendas.
Fórmula: Total Vendas = SUM(fVendas[Valor Venda])
Também vamos inserir uma matriz para visualizar as medidas que criaremos daqui pra frente. Vamos inserir o campo de Data nas linhas e a medida Total Vendas na coluna da matriz. Veja:
Acumulado anual – Year To Date
Acumulado anual com DATESYTD
Vamos criar uma medida que vai calcular o total acumulado de vendas até o final de determinado período do contexto. Veja:
Fórmula: Total Vendas YTD = CALCULATE( [Total Vendas], DATESYTD( dCalendario[Data] ) )
DATESYTD: Retorna todas as datas do ano, desde o primeiro dia até o máximo da ‘data’ (dia, mês, ano) do contexto.
Após alterar o formato da medida para Moeda, editar o número de casas decimais para 2 e adicioná-la na nossa matriz, teremos o seguinte resultado:
Perceba que a soma dos três primeiros meses da coluna Total Vendas é igual ao valor do terceiro mês (março) da coluna Total Vendas YTD. Então, o que essa medida está fazendo é acumular os valores desde o primeiro dia do ano até o máximo do dia do contexto (nesse exemplo é março).
Fazendo o drill down para apresentar também os dias, veja como fica a coluna Total Vendas e como é realizado o cálculo internamente:
Note que há uma função que também retorna o mesmo valor da medida que acabamos de calcular, veja:
Fórmula: Total Vendas YTD = TOTALYTD( [Total Vendas], dCalendario[Data] ) )
Veja que resultou o mesmo valor que tínhamos encontrado antes:
Particularmente não gosto de utilizá-la porque ela esconde o que tem por trás dela (que é o uso do CALCULATE e do DATESYTD). Além disso, usando a primeira versão (com CALCULATE) eu poderia passar filtros como argumento, enquanto na versão usando TOTALYTD não.
Ah, mas eu não poderia já trazer o cálculo Total Vendas YTD da minha base de dados?
Poderia sim. Vamos mostrar um problema frequente quando você opta por fazer isso.
Vamos supor que a gente traga da base de dados esse valor Total Vendas YTD já calculado. Adicionando essa coluna na matriz, compare a coluna que tínhamos (medida) com a coluna importada da nossa fonte de dados:
Veja que os valores não correspondem na figura acima (quando olhamos a matriz na granularidade mensal). Note que quando arrastamos a coluna importada para a matriz, foi realizado o somatório dos dias (dessa mesma coluna).
Veja que ao fazer o drill down para visualizar os dias (granularidade diária), as duas colunas estão iguais (porque não há um nível depois dias – horário, por exemplo):
Veja que quando arrastamos uma coluna para um visual, o Power Bi já configura essa coluna pra ser somada. Poderíamos então configurar essa coluna para obter o valor máximo, certo?! Vamos fazer isso! Clique com o botão direito na coluna (Valores do Visual) e mude de Soma para Máximo. Observe que agora os valores da medida e coluna são iguais, porém a linha de total (a última da matriz) continua errada.
Um outro problema quando trazemos colunas já calculadas da nossa base é que ao adicionarmos, por exemplo, um filtro de País, a coluna não respeitará esse filtro externo. Veja que quando filtrarmos qualquer país, a coluna Máximo de Total Vendas YTD permanecerá sem alterações enquanto a coluna com nossa medida Total Vendas YTD considerará o filtro aplicado corretamente.
Importante:
Os maiores problemas de trazer contas “prontas” da fonte de dados são relacionados à granularidade (nível de análise) e ao filtro (os valores ficam estáticos).
Vamos para a nossa próxima medida!
Variação mensal (% MoM)
Nosso objetivo agora será calcular a variação no valor das vendas em relação ao mês passado (month over month). Ou seja, precisamos de duas informações: o valor que tinha no mês anterior e qual valor do mês atual (contexto atual).
Last Month com DATEADD
Sempre que quisermos criar uma medida em DAX temos que ter em mente que todos os valores que ela utilizar devem estar visíveis no contexto. Ou seja, se quisermos calcular a variação mensal, precisamos trazer o valor do mês anterior para a mesma linha (contexto) que estamos calculando essa variação. Então precisamos primeiro trazer esse valor passado para o contexto ‘presente’. Usaremos a função DATEADD assim:
Fórmula: Total Vendas LM = CALCULATE ( [Total Vendas], DATEADD ( dCalendario[Data], -1, MONTH ) )
DATEADD: É muito útil para calcular a porcentagem de crescimento ao longo do tempo. É utilizada para avançar ou retroceder no tempo (anos, meses, dias, etc).
Veja que na figura acima Total de Vendas de fevereiro foi levado para a linha do mês de março (retrocedemos 1 mês no contexto) bem como o Total de Vendas de Maio foi levado para a linha do mês de Junho da coluna Total Vendas LM.
% MoM com DIVIDE
Agora sim, vamos calcular a variação mensal (mês sobre mês) com o uso da função DIVIDE. Como queremos determinar a variação, precisamos calcular a diferença entre os dois valores e depois dividir essa diferença pelo último. Veja a sintaxe:
Fórmula: % MoM = DIVIDE( [Total Vendas] - [Total Vendas LM], [Total Vendas LM] )
DIVIDE: É utilizado para realizar a divisão entre dois valores. A diferença entre usar ele e a função de dividir (/) é que usando DIVIDE, caso seu denominador seja zero, por padrão o valor resultante será Blank (vazio) a não ser que você especifique um outro valor no terceiro argumento da função.
Verifique que na matriz da figura acima, a coluna com a medida % MoM está em branco (Blank). Lembra que a sintaxe de DIVIDE pede 3 argumentos? Como não especificamos nada no terceiro argumento, ele retorna por padrão Vazio. Olhando para a linha de março da matriz, o cálculo foi realizado corretamente.
DATEADD considera o período ( nesse caso: mês) cheio ou o contexto?
Participante da Live
Considera o contexto. Veja que o Total Vendas LM trouxe o valor do Total Vendas de 8/Fev (retroagindo exatamente 1 mês). Veja a figura abaixo:
Se quiséssemos repetir o valor do Total de vendas do mês de janeiro em todas as linhas (dias) do mês seguinte, poderíamos usar a função PREVIOUSMONTH. Veja a sintaxe:
Fórmula: Total Vendas PM = CALCULATE( [Total Vendas], PREVIOUSMONTH ( dCalendario[Data] ) )
Observe que conseguimos obter o Total de Vendas de janeiro em todas o linhas do contexto de fevereiro na coluna Total Vendas PM.
Variação Anual (% YoY)
Semelhante ao que fizemos para meses, vamos primeiro obter o Total de Vendas do ano anterior:
Fórmula: Total Vendas LY = CALCULATE( [Total Vendas], SAMEPERIODLASTYEAR( dCalendario[Data] ) // DATEADD( dCalendario[Data]; -1; YEAR) )
Note que dessa vez utilizamos a função SAMEPERIODLASTYEAR, mas se utilizássemos DATEADD conforme trecho comentado (leia a linha após as duas barras //), teríamos o mesmo resultado. Usamos DATEADD na medida Total Vendas LY porque não existe (ainda) uma função semelhante para mês, beleza?!
Valor acumulado
Se precisássemos acumular um valor desde o primeiro dia até o último dia de vendas, como poderíamos fazer isso? Infelizmente não há uma função pronta para isso, então vamos criar a seguinte medida e utilizar variáveis para nos ajudar:
Fórmula: /*Opção 1: sem usar a função FILTER */ Total Vendas Acumulado = VAR vUltimoDiaContexto = MAX(dCalendario[Data]) VAR vAcumulado = CALCULATE( [Total Vendas], ALL(dCalendario), dCalendario[Data] <= vUltimoDiaContexto ) ) RETURN vAcumulado /*Opção 2: utilizando a função Filter */ Total Vendas Acumulado = VAR vUltimoDiaContexto = MAX(dCalendario[Data]) VAR vAcumulado = CALCULATE( [Total Vendas], FILTER( ALL(dCalendario), dCalendario[Data] <= vUltimoDiaContexto ) ) RETURN vAcumulado
A função ALL faz com que toda a tabela de Datas da tabela dCalendario seja retornada, certo? Também sabemos que temos que somar o valor de vendas de forma que essa soma pare somente no último dia daquele contexto. Por isso, utilizamos a variável vUltimoDiaContexto! Junto da função FILTER será possível somar [Total Vendas] de forma que essa soma “pare” em cada contexto especificado (mas não tenha que reiniciar do zero).
Nosso objetivo era manter o cálculo do acumulado mesmo quando virar o ano. Veja, que nossa medida anterior (Total Vendas YTD) tem formato de serra – o valor é acumulado de o primeiro dia de Janeiro até o último dia de Dezembro pra cada ano, momento que a curva cai – note que no ano seguinte, esse valor inicia do zero… Com essa nova medida (Total Vendas Acumulado), conseguimos acumular os valores desde o primeiro dia até o último de cada contexto. Veja graficamente a comparação das duas medidas (em Fev/2019 tivemos nossa última venda – valor constante a partir dessa data):
E se quisermos acumular os valores por produto?
Participante da Live
Já fizemos um vídeo sobre como montar um gráfico Pareto (calculamos o valor acumulado por produto lá). Clique aqui para ver.
Bom, agora já estamos prontos para nosso desafio!
#Desafio: Churn MRR YTD
Nesse desafio vamos responder à seguinte questão: quanto vou deixar de ganhar em função da perda de clientes que cancelaram contrato com minha empresa ?! Qual o valor total dessa perda no final do ano?! Ou seja, objetivo será calcular o Churn MRR YTD.
Dica:
Churn é o valor que perdi com clientes que saíram da minha empresa (cancelaram o contrato);
MRR (Monthly Recurring Revenue) é um tipo de receita recorrente. Ex: Se você possui clientes que assinam algum contrato (com duração de n meses) com sua empresa e em troca você presta algum serviço, essa receita pode ser denominada MRR.
Vamos ver no Excel rapidamente qual a ideia principal do que precisamos fazer em DAX:
Vamos supor que tivemos esses valores de perda de receita da coluna B. Como devemos estimar qual seria a perda de receita anual acumulada (em dezembro), ou seja, qual valor estamos deixando de ganhar com a perda desses clientes (receitas ou contratos) até o final do ano?
Criaremos a seguinte medida:
Fórmula: Churn MRR YTD v2 = VAR MesContexto = MAX(dCalendario[Mês]) VAR ProjecaoAno = SUMX( DATESYTD(dCalendario[Data]), //Para cada contexto de mês eu preciso varrer novamente desde o início do ano, até o último dia do contexto VAR MesIteracao = CALCULATE(MAX(dCalendario[Mês])) RETURN [Churn] * (MesContexto - MesIteracao + 1) ) RETURN ProjecaoAno
A lógica que temos que pensar é: olhando para a perda de receita em jan/2019, preciso acumular ‘essa perda’ em 12 meses; o valor de fev/2019 em 11 meses; o de março em 10 meses e assim por diante (veja a fórmula do Excel para ajudar). Então imagine que estamos na linha do contexto de mar/2019 (lembra que em março preciso acumular o valor nos 10 meses seguintes?): a fórmula deve nos fornecer o acumulado de 12 meses – 3 meses + 1. É exatamente o que mostra aquele trecho da fórmula MesContexto – MesIteracao + 1.
Observem como ficou nossa solução:
Olhe só para a linha de Março! Até março sabemos quanto de perda tivemos, certo? Basta acumularmos de Jan e Mar os valores de Churn: veja o uso daquela função que aprendemos hoje, a DATESYTD. Agora, para saber os próximos valores (das linhas que a coluna Churn encontra-se vazia) precisamos também de uma variável iteradora: a MesIteracao. Para entenderem essa solução por completo sugiro que ‘namorem’ bastante essa fórmula! Não é fácil entendê-la no início, beleza?!
Vejam que bacana ficou o gráfico quando analisamos os 3 anos (removendo o filtro de ano =2019 que estava aplicado na figura anterior)… Note que o valor de “perda” de receita cresce ao longo do tempo de forma quase que exponencial. Esta aí a importância de analisar a perda de clientes no acumulado!
Comparação de períodos incompletos
Agora, vamos resolver uma dúvida: como comparar valores de períodos incompletos (anos). Veja que possuímos uma coluna com o Total de Vendas e o Total de Vendas LY (referente ao ano anterior). Na linha em negrito da matriz temos dois valores não comparáveis (já que um representa o valor de vendas de 3 meses de 2019, enquanto o outro representa o valor de vendas de 12 meses do ano anterior, isto é, o ano está completo). Por consequência o cálculo de % YoY fica muito abaixo,veja:
Nossa solução será criar uma coluna calculada na dCalendario verificando se há ou não vendas, veja:
Fórmula: Possui Vendas? = dCalendario[Data] <= Max(fVendas[Data Venda])
Veja que essa fórmula é booleana, ou seja, já retorna True ou False. Agora basta usar essa coluna no Filtro lateral (Visual) e marcar a opção “True”.
Viu como foi simples?! Agora não teremos mais problemas com datas futuras sem vendas no nosso visual e consequentemente valores de anos não completos sendo comparados inapropriadamente!
Bom galera, esse foi nosso conteúdo de hoje baseado na Live #2! Espero que tenha sido útil pra vocês!
Um abraço,
Leonardo.
