Recente os dataholics tiveram um desafio e não encontraram nenhum material em português que pudesse ajudar a traçar o melhor caminho. Após muitas pesquisas, encontraram um conteúdo que o ajudaram a solucionar. A partir disso, com o objetivo de ajudar outros profissionais que possam passar pelo mesmo desafio, Luiz Otávio escreveu este artigo:
Sumário
Artigo em português
Fala pessoal, tudo bem? Espero que sim.
Sou Luiz Otávio, consultor de Business Intelligence aqui na Dataside e hoje, vou falar de uma solução construída no Power BI usando DAX. Essa solução se refere a um desafio que tivemos em um projeto junto ao nosso cliente.
O desafio era: “Filtrar os últimos 12 meses de vendas a partir de um filtro de mês selecionado”. No primeiro momento pensei: “Wow, como farei isso em DAX?”. Vem comigo que te explico como resolver esse problema.
Inicialmente, realizei diversos desenvolvimentos, nos quais infelizmente não tive sucesso. Procurei materiais em português e encontrei pouquíssimo conteúdo que pudesse me ajudar a resolver o desafio. Ou seja, há uma carência de material em solo brasileiro. Sendo assim, realizei pesquisas em inglês e, para minha sorte, encontrei o riquíssimo material do Jason Thomas em seu blog SQL Jason, o qual recomendo que você também consulte. E então eu já gostaria de dizer ao Jason: Obrigado, amigo!!
Ok, vamos agora então ao desenvolvimento da solução.
Entendendo os dados e o cenário:
Primeiramente, observe que tenho duas tabelas: uma tabela fato denominada “fVendas” e uma dimensão, denominada “dCalendário”. Como podemos ver na imagem abaixo, há um relacionamento entre elas. E caso você não tenha uma tabela dimensão de Datas em seu modelo de dados, pare a leitura por aqui e crie uma.
Posteriormente, observe que em minha tabela fato, há uma coluna de Mês/Ano. E é obrigatório tê-la. Você vai entender o porquê no último tópico do artigo “Visualizando o resultado”.
Desenvolvendo o cálculo DAX:
Vamos iniciar nossa medida DAX através de variáveis. Criando a primeira variável “MaxFacDate” – basicamente pedimos ao Power BI que ignore o filtro de data que aplicamos no slicer e encontre o maior valor de Data na coluna Data Faturamento, da tabela fVendas.
Depois, criamos outra variável – Essa variável, através da função ENDOFMONTH, nos permite encontrar o último dia do mês referente ao mês filtrado no slicer.
E na nossa última variável – usamos a função EDATE para dizer assim ao Power BI: “Quero que você busque qual é o último dia de 11 meses atrás, com base no mês filtrado pelo slicer”. Observe que o primeiro argumento da função EDATE é a nossa última variável criada FDate.
E para finalizar nossa solução, aplicamos um return.
No return, dizemos assim ao Power BI: “Se, o maior valor de data na tabela fVendas for menor ou igual ao maior valor de data na tabela dCalendário E se, o maior valor de data na tabela fVendas for maior que a variável Edate, então eu quero que você faça um cálculo alterando o contexto de filtro da minha medida Soma de Vendas ignorando o filtro da tabela dCalendário.
Caso nada disso seja verdadeiro, eu quero que você apenas retorne a medida Soma de Vendas, sem nenhuma alteração de contexto”.
Visualizando o resultado:
Observe o mês no slicer: Setembro/2018. Veja no gráfico os últimos 12 meses a partir do mês selecionado.
Obs.: no eixo X do gráfico, deverá ser usado a coluna Mês/Ano da tabela fato e não da dimensão, pois no cálculo do return, pedimos que ele ignore qualquer filtro vindo da tabela dimensão dCalendário.
Isso é tudo pessoal. Espero que esse material possa ajudá-los, como também me ajudou. E mais uma vez, obrigado Jason Thomas, nos ajudou a criarmos um grande resultado de valor ao nosso cliente.
________________________________________________________________________________
Artigo em inglês
Hey folks, how is everything? I hope you’re doing well.
I am Luiz Otávio, Business Intelligence Consultant here in Dataside and today I’m going to speak about a solution built in Power BI using DAX. This solution refers to a challenge we had in a Project alongside our client.
The challenge was: “Filter the last 12 months of sales from a selected month filter”. At first, I thought: “Wow”, how am I supposed to do that in DAX?” Hop in and I’ll explain you how to solve this problem
Initially, I performed several developments which unfortunately were unsuccessful. I looked for materials in Portuguese, and found very few contents that could help me to solve the challenge. That is, lack of material on Brazilian soil. So, I did some research in English and luckily, I found the very rich material by Jason Thomas on his SQL blog Jason. So, I would like to say to Jason: Thank you, my friend.
Ok, now let’s get to the development of the solution.
Understanding the scenario
First, notice that I have two tables: a fact table called fVendas and a dimension called dCalendar. As we can see in the image below, there is a relationship between them. And if you don’t have a Dates dimension table in your data model, stop reading here and create one.
Later, note that in my fact table, there is a Month / Year column. And it is mandatory to have it. You will understand why in the last topic of the article “Visualizing the result”.
Developing measure in DAX
Let’s start our DAX measure through variables. Creating the first variable “MaxFacDate”. Basically, we asked Power BI to ignore the date filter we applied to the slicer and find the highest Date value in the Billing Date column of the fSales table.
Then, we create another variable. This variable, using the ENDOFMONTH function, does not allow you to find the last day of the month for the filtered month in the slicer.
And in our last variable, we used the EDATE function to say to Power BI, “I want you to have a lookup for the last day of 11 months ago, based on the month filtered by the slicer.” Notice that the first argument of the EDATE function is our last created variable FDate.
And to end our solution, we apply a return.
On the return, we say to Power BI: “If the largest date value in the fVendas table is smaller or equal to the largest date value in the dCalendar table And if the largest date value in the fVendas table is greater than the Edate variable, then I want you to do a calculation by changing the filter context for my Sum of Sales measure bypassing the filter in the Calendar table.
If none of this is true, I want you to just return the Sum of Sales measure, without any change in context.
Visualizing the result
Observe the month in the slicer: September/ 2016. See the graph for the last 12 months from the selected month.
Note: in the X axis of the graph, the Month / Year column of the fact table should be used and not of the dimension, since in the calculation of the return, we ask that it ignore any filter coming from the dimension table.
That’s all folks. I hope that this material can help you as it helped me. And again, thanks Jason Thomas, you helped us to create a great result of value for our client.