cancel
Showing results for
Did you mean:
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

## Re: Month Slicer not working for Running Total

Perhaps:

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

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

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

Proud to be a Super User!

Highlighted
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