Future-Proof Your Career, Master Data Skills + AI

Blog

Blog

# DAX Examples In Power BI – Advanced DAX Formulas

by | 7:00 pm EDT | October 30, 2020 | DAX, Power BI

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.

## 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

## How to Calculate Age in Excel: 5 Best Methods Explained

Looking to calculate age in Excel? Well, you're in the right place. Whether you need to find the age of...

## Funny ChatGPT Prompts: 20 Hilarious ChatGPT Ideas

In a world where technology continues to amaze us, we have now arrived at the point where we can have a...

## Power BI Slicer Search: User Guide With Examples

Ready to get started with the Power BI slicer? This feature will allow you to filter and slice your...

## SUMPRODUCT Multiple Criteria: Explained With Examples

Most Excel users think that the SUMPRODUCT function in Excel helps only to multiply the numbers in...

## Data Analytics Outsourcing: Pros and Cons Explained

In today's data-driven world, businesses are constantly swimming in a sea of information, seeking the...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## The Top 5 Power BI Alternatives in 2023

Power BI has established itself as a powerful business analytics platform, offering a wide range of...

## Power BI Waterfall Chart: A Detailed User Guide

In the world of data visualization, charts speak louder than numbers. If you're looking for a way to...

## Power BI Import vs Direct Query: Which is Better & Why?

In the world of data analysis, Power BI offers you a range of tools to connect to your data sources....

## Power BI Certification: Everything You Need to Know

In today's data-driven world, the ability to transform raw numbers into meaningful insights is more...

## Power BI Bookmarks: The Ultimate Guide

When working with data, bookmarks offer a streamlined and personalized way to navigate through large...

## Power BI Default Slicer Value Explained

One of the key features of Power BI is the slicer, which allows you to filter your data based on...