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