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

Table of Contents

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

## Extended Date Table Power Query M Function

In today's blog post, we'll take another look at the Power Query M function for the extended date...

## Rule Of Thirds: The Composition Rules

When it comes to presenting the story, we need to start with the layout. This is where the rule of...

## Evaluation Context In DAX Calculations

In this tutorial, we'll learn about evaluation context in DAX. Evaluation or initial context is the...

## Icons In Power BI | DAX, UNICHAR, UNICODE & Custom Images

Icons in Power BI are used to communicate meaning and add more context. You can use them as an...

## How To Compare Two Lists Of Calculated Data Virtually – An Advanced DAX Technique

When working with calculated data, comparing different data sets will sometimes be necessary. I'm going...

## Heat Map – A Great Visualization For Power BI Reports

In this tutorial, youâ€™ll learn how to create a heat map visual using Charticulator. It is used to...

## CALCULATE Function – How It Can Affect Your Calculations On Power BI

In this tutorial, I want to show you what the CALCULATE function can do through a few examples....

## Remove Empty Columns In Power BI

This blog will demonstrate how to automatically remove all empty columns in Power BI through the Power...

## DAX Calculation Groups – Power BI Report for Problem Of The Week #7

I'm going to talk about the solution I came up with for the 7th Problem of the Week. The problem...

## Publishing PowerApps Applications and Changing The Screen Order

In this tutorial, we're going to talk about publishing PowerApps applications and making sure the...

## Power BI Financial Reporting: Allocating Results To Templates At Every Single Row

Here I want to showcase a unique idea around financial reporting, which is allocating results to...

## Optimizing Queries For A Faster DAX Performance

This tutorial will talk about optimizing your queries in DAX Studio. You'll also learn how to mitigate...