Show Cumulative Totals Unaffected By Date Slicer Selection In Power BI

No comments

In this blog post, you’ll learn how to show data from specific time frames as filtered by a date slicer without affecting the overall Cumulative Totals. This is based on a query on the Enterprise DNA Members-Only Support Forum. For sure, you’ll find this quite useful when facing a scenario similar to the example that will be given below.

I have answered this query in the support forum, but I want to show you how I was able to put it all together.

The Scenario: Actuals Vs Budget

Basically, the member who posted the query has data comparing Actuals versus Budgets.

What this member wants to do is to show only the Cumulative Total for a specific time period without affecting any of the original results.

So let’s say the member only wants to show the totals for January, February and March.

We’re hoping that the Cumulative Total for those specific months will be shown while the Cumulative Total for the rest of the year remains on the visualization as well.

Of course, without the right logic, every time the date slicer is moved, the entire report page shifts as well. That’s the problem that I’m going to solve here.

Creating A New Date Table

To start off, I set up a similar scenario. As expected, everything in my visualization changed when I selected four months on my date slicer.

Since I only want to change a specific part of the visualization, I would need to create a brand new date table. This table is going to dictate the individual result that I need. I called the new table Filter Dates, which is an exact replica of the original Dates table.

I did not build any relationships between the new date table and the other core tables in the model. That’s because we’re only trying to filter a single result in this scenario.

Also, note that we don’t want all the other data to be affected by any changes to the date slicer. A relationship here would make our attempt ineffective.

To make sure I don’t confuse the new date table with the original one, I named this Filter MonthYear.

Now that there’s a new date table, I’m going to change the slicer on my report page. Again, this is the filter that has no connection to the rest of my model.

Note that you also have to set a Max Filtered Date in the particular selection that you’re making.

A Max Filtered Date makes the formula work. So if the Date you entered into the date slicer is less than or equal to the Max Filtered Date, you would get the Cumulative Total you’re looking for. If not, you get a BLANK.

When you look at the visualization, you’ll see that I now have an entire area here where there are no results. This reflects the data that turned out BLANK.

Now, we can retain the year’s totals from a budgeting perspective. But at the same time, we can also manipulate the time frame that we want in our individual results.

Conclusion

Hopefully, you find some value in the technique I showed for similar scenarios you’ll be facing. Just remember that the key thing here is to solve all this right inside the model. Otherwise, you’ll create issues on other parts of your report pages.

Always look at the logic you’re adding into your formula. This way, you can pinpoint which part of your model you’re making an impact to. In this case, we’ve succeeded in making the Cumulative Totals dynamic based on the new date slicer we created.

All the best,

Sam

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

***** Related Links *****
How To Harvest Power BI Slicer Selections To Use Within Other Measures
Filtering Data By Custom Fiscal Years And Quarters Using Calculated Columns In Power BI
Cumulative Totals Based On Monthly Average Results In Power BI

***** Related Course Modules *****
Time Intelligence Calculations
DAX Formula Deep Dives
Advanced Data Transformations & Modeling

***** Related Support Forum Posts *****
Date Slicer Default To Fiscal Year
Date Slicer Restriction
Date Slicer Not Updating With Schedule Refresh
For more date slicer support queries to review see here…

Enterprise DNA Events


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.