Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm trying to make a running total measure disregard the month slicer selection.
I have this base dataset:
Here is my running total without month selection (works fine):
The problem comes when I click on the slicer, like this:
I want it to look like this (photoshopped):
In other words, I want the slicer to affect the Actual column, but not the Running Total of the actual.
Here is the Dax code I have of the running total:
RT of Actual = CALCULATE( SUM('Dataset'[Amount]) ,FILTER( ALLSELECTED('Dataset'[MonthID]), ISONORAFTER('Dataset'[MonthID], MAX('Dataset'[MonthID]), DESC) ) ,'Dataset'[Capex Type] = "Act" )
Solved! Go to Solution.
First, please turn MonthID into an integer (of the same shape as your text) or add a column that will store the same value as integer (but I'd go with the first option) and HIDE IT.
Here's the measure but please do the above:
-- measure 1 [Amount] := SUM('Dataset'[Amount]) -- measure 2 RT of Actual = var __lastVisibleMonthID = MAX( 'Dataset'[MonthID] ) return CALCULATE( [Amount], ALL( 'Dataset'[Month] ), 'Dataset'[Month] <= __lastVisibleMonthID, 'Dataset'[Capex Type] = "Act" )
Best
Darek
First, please turn MonthID into an integer (of the same shape as your text) or add a column that will store the same value as integer (but I'd go with the first option) and HIDE IT.
Here's the measure but please do the above:
-- measure 1 [Amount] := SUM('Dataset'[Amount]) -- measure 2 RT of Actual = var __lastVisibleMonthID = MAX( 'Dataset'[MonthID] ) return CALCULATE( [Amount], ALL( 'Dataset'[Month] ), 'Dataset'[Month] <= __lastVisibleMonthID, 'Dataset'[Capex Type] = "Act" )
Best
Darek
Thank you so much. This worked like a dream!
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |