Forum Discussion
Modifying Cumulative Measure
HI,
I want try and modify the below cumulative measure to ignore dates after a certain point (Today's date). The measure provides a cumulative sum of a value against the report date. I want to try and stop it after a certain point as it is using dates which aren't required.
Try using this :
Cash In Cumulative =
VAR MaxReportDate = MAX('Cash_View'[Report Date])
VAR CutOffDate = TODAY()RETURN
CALCULATE(
SUM('Cash_View'[Amount]),
FILTER(
ALLSELECTED('Cash_View'[Report Date]),
ISONORAFTER('Cash_View'[Report Date], MaxReportDate, DESC)
&& 'Cash_View'[Report Date] <= CutOffDate
),
'Cash_View'[InOut] = "Cash In"
)
9 Replies
pankaj_lpHelper I
what exactly is the certain point, is there a fixed date?
- pankaj_lp
Try using this :
Cash In Cumulative =
VAR MaxReportDate = MAX('Cash_View'[Report Date])
VAR CutOffDate = TODAY()RETURN
CALCULATE(
SUM('Cash_View'[Amount]),
FILTER(
ALLSELECTED('Cash_View'[Report Date]),
ISONORAFTER('Cash_View'[Report Date], MaxReportDate, DESC)
&& 'Cash_View'[Report Date] <= CutOffDate
),
'Cash_View'[InOut] = "Cash In"
)Thanks for the reply pankaj_lp , so i've used the DAX above. It still seems to be using the maximum date from the table (25/09/2023) instead of using "CutOffDate".
For context, this measure is going on a line graph with other similar measures filtered by different [InOut] values, e.g. "Cash In", "Cash Out".etc.
Apply a filter to the datasource as a whole isn't possible in this scenario as some of the measures do need to take into account a date beyond today for "Forecast" values
Neil
- pankaj_lp
What is date filter used in this case, any date slicer used?
Hi pankaj_lp ,
No there's no other date filters used, as an update, i tried to change MaxReportDate to
CALCULATE(MAX(EXVW_Cash_Combined[Report Date]),EXVW_Cash_Combined[Report Date]<TODAY()), however this prevents the line from plotting the cumulative values for each [Report Date] and just gives the total
The data table looks like this, its just amounts with "Cash In/Cash Out" for transactions which have taken place, and "Forecast In/Forecast Out" for future amounts. The [Report Date] is a week commencing date
- pankaj_lp
try changing the maxreportdate to today() itself
It has the same effect, it flattens the line and can't correctly plot the week by week cumulative sum
