cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Month Slicer not working for Running Total

I am trying to calculate the running total of a set of measures by months in my matrix. The running total is working by itself, however when used with my monthly slicer it breaks. I want it to calculate up to the month selected in the slicer, but instead it filters for that specific month.

example: when I select May I want to see running total froo every month from Jan to May but it shows me data only in the month of May.
Running Total = CALCULATE
                             ( SUM ( Table[column] ),
                                FILTER ( ALLSELECTED( Table ),
                                  Table[columnDate] <= MAX ( Table[ColumnDate] ) ) )

The Month Slicer comes from Table[ColumnDate].Month

2 REPLIES 2
Highlighted
Super User IX
Super User IX

Re: Month Slicer not working for Running Total

Perhaps:

Running Total = CALCULATE
                             ( SUM ( Table[column] ),
                                FILTER ( ALLEXCEPT( Table[ColumnDate ),
                                  Table[columnDate] <= MAX ( Table[ColumnDate] ) ) )


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User V
Super User V

Re: Month Slicer not working for Running Total

Hi,

Here's my approach:

  1. Create a Calendar Table and write calculated column formulas to extract Year, Month Name and Month Number.  Using the Month number column, use the "Sort By" column feature to ensure that the Month Names are sorted
  2. Create a relationship from the ColumnDate field of the Table to the Date column of the Calendar Table
  3. Create 2 slicers - one each for Month Name and Year.  Select 2020 and May in those slicers
  4. Write these measures

Total = SUM(Table[Column])

Total YTD = CALCULATE([Total],DATESYTD(Calendar[Date],"31/12"))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors