Cumulative Totals Across Averages & Dynamic Date Logic – Advanced DAX

No comments

Today I wanted to cover a unique technique around cumulative totals inside of Power BI.

Usually, when we are reviewing cumulative totals we are analyzing them over a date or over a month and year. What we are going to do here though, is we’re going to analyze them versus the month.

This particular example stems from a very interesting topic at the Enterprise DNA Support Forum. Check this out if you want to review more.

The interesting thing about this particular technique is that when you have a context of just the month then you need to account for different years being selected. This could occur via a slicer selection or a page level filter.

To solve this takes a technique slightly different to what you may be thinking. The reason for completing it this way is because we also need to make sure that the totals are correct and that they dynamically adjust for different date selections which may be coming from a users’ slicer selection.

In my ultimate solution I use a combination of DAX formulas including SUMX and SUMMARIZE, but what you also need to do is make sure that you wrap these particular functions with a FILTER statement.

That filter statement is what will enable you to ultimately get the correct cumulative total and a dynamic one at that.

There’s a bit to learn in this particular tutorial but it’s really interesting content and I hope you agree.

When you learn how to combine a lot of DAX functions together inside of Power BI, then solving these unique scenarios become very achievable.

Good luck diving into this one.

Sam.

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s