In this tutorial, I’ll show you some advanced formulas and DAX examples using different functions in Power BI. Some of the functions that you’ll encounter in the examples below are CALCULATE, FILTER, MAX, ALLSELECTED, and many more.
But before you begin, make sure to optimize your measures. I always recommend using measure groups to make things more intuitive inside your reports. This way, you can easily branch out to the more advanced measures.
DAX Examples: Calculating Cumulative Total
The first DAX calculation that I’m going to show you is an example of a cumulative total.
Using cumulative total is a great way of showcasing a comparison over a long period of time.
The sample visualization below looks busy that you can hardly see the underlying trend. But if you use a cumulative total pattern, you’ll be able to see the important insights more clearly.
You need to start the formula for Cumulative Sales using the CALCULATE function. With this function, you can easily calculate the total sales using a different context.
Then, you need to use FILTER along with ALLSELECTED function to reference the Dates table.
After that, use the MAX function to evaluate every single row and context in the table. It will check if the date is less than or equal to the current date of the particular row. If that’s the case, the MAX function will calculate the sales of all those dates.Â
The formula will also iterate through every single day of the Date column using the ALLSELECTED function. This is the basic pattern of cumulative formulas in Power BI.
You can now bring in the Cumulative Sales measure we just created to the table.
Basically, the cumulative total calculation will include all of the earlier rows. For example, the data in the fourth row is the total of the first three rows. Similarly, the fifth row calculates the first four rows.
The concept of virtual tables is also applicable in this DAX calculation example. For every single row, the formula creates a virtual table for all the prior dates and sales. And then, it calculates all the sales of those dates. The formula will run this pattern over and over again until the last row of the table.
DAX Examples: Calculating Cumulative Sales Last Year
Let’s move on to another advanced DAX calculation example. This time, I’ll show you how to calculate the Cumulative Sales Last Year.
Just copy the previous formula for Cumulative Sales and rename it accordingly. Instead of Total Sales, you need to reference the Sales LY inside the CALCULATE function.
When you check the results for Cumulative Sales LY in the table, you can only see the cumulative sales from last year. It’s doing the same virtual calculation and changes the context of every single row due to the CALCULATE function.
This time, you can change the existing visualization below to showcase the cumulative results.
You can use the Area Chart visualization here and you can also select something from the slicer for additional context.
Here’s what the new visualization looks like.
For example, if you select something from the City slicer, the results in the visualization changes. Here’s a comparison below.
DAX Examples: Calculating Cumulative Sales Difference
The last thing that I want to find out is how we performed this year compared to last year cumulatively. You can do this by simply finding out the difference between Cumulative Sales and Cumulative Sales LY.
After that, you can bring the Cumulative Difference measure to the bottom visualization.
With this, you can clearly see the trend in terms of the performance of the current year compared to the previous year.
What’s interesting about this advanced DAX example is that you can use this to create another time comparison analysis. Since you used the YEAR option from the initial sales measure, you can change it to DAY, MONTH, or QUARTER.
***** Related Links *****
Segmentation Example Using Advanced DAX In Power BI
Calculating A Cumulative Run Rate Using DAX In Power BI
Dynamically Calculate A Power BI Running Total Or Cumulative Total
Conclusion
The DAX examples above are great applications of advanced Power BI calculations. We combined formulas and integrated time intelligence calculations.
You can easily branch out into other time comparisons after learning the above mentioned DAX examples. Just apply all the insights and techniques that you have learned from this.
Sam
