Modifying Cumulative Measure

[rsrgerge]

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.

Cash In =

CALCULATE(

    SUM('Cash_View'[Amount]),

    FILTER(

        ALLSELECTED('Cash_View'[Report Date]),

        ISONORAFTER('Cash_View'[Report Date], MAX('Cash_View'[Report Date]), DESC)

    ),Cash_View[InOut]="Cash In"

)

Thanks for any and help provided

[pankaj_lp]

try changing the maxreportdate to today() itself

[rsrgerge]

It has the same effect, it flattens the line and can't correctly plot the week by week cumulative sum 

[rsrgerge]

@pankaj_lp 

Solved it thanks to your help, I realised i had to apply the filter to the variable to get the Max Report Date relative to that value type. Thanks for your help!

MaxReportDate = CALCULATE(MAX(Cash_View[Report Date]),Cash_View[InOut]="Cash In")

[pankaj_lp]

What is date filter used in this case, any date slicer used?

[rsrgerge]

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 · Accepted solution]

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"
)

[rsrgerge]

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

[rsrgerge]

Hi

@pankaj_lp 

Yes it'd be todays date.

Neil

[pankaj_lp]

what exactly is the certain point, is there a fixed date?