DAX Examples In Power BI – Advanced DAX Formulas

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

Enterprise DNA Power BI On-Demand

The Latest

As you continue your journey as a Python programmer, you’ll want to write code that is more efficient, readable, and…

Python Inline If: Simplify Your Conditional Expressions

You’ve been cruising through your Python journey, slicing through lists, taming those wild tuples, and maybe even wrestling with a…

Python Empty String: Understanding and Handling It Effectively

Power BI financial dashboards provide a quick and easy way to monitor an organization’s financial performance in real-time. By consolidating…

Power BI Financial Dashboard Examples: Key Insights for Businesses

When working with integers in Python, you should know the maximum value your code can handle. This will depend on…

Python Max Int: Understanding Maximum Integer Limits

Pi is a fundamental mathematical constant that represents the ratio of a circle’s circumference to its diameter. Leveraging Pi in…

4 Ways to Use Pi in Python With Examples

ChatGPT is an advanced AI-powered tool that can transform the way you write code. Developed by OpenAI, ChatGPT accelerates your…

ChatGPT for Coding: A Guide With Practical Examples

When working with data projects in Python, you are most likely to use datetime functions. Almost every dataset that you’ll…

Python datetime, a comprehensive guide with examples

Power BI is a powerful business analytics tool that helps you visualize and analyze data from various sources. One of…

Power BI Themes: How to Customize Your Reports with Ease

With the advent of ChatGPT, individuals and businesses worldwide have been using it to simplify their daily tasks and boost…

ChatGPT for Data Scientists: Unleashing AI-driven Insights

Staying ahead of the curve in data analysis is essential to your success in business. One of the most innovative…

ChatGPT for Data Analysts: Revolutionizing Insights and Reporting

Imagine being able to look at your data from every which way — from the bird’s eye view right down…

Power BI Hierarchy: Unlocking Levels and Drill-Downs in Visualizations

As you explore Python and take on more complex projects, you’ll notice that some projects have lots of files. Managing…

os.path.join: Simplifying File Path Operations in Python

Load More