Need help with measure: Reset cumulative total on specific dates

[Anonymous]

Hello,

I have a cumulative total that I need to reset on specific dates.

The specific dates are in a different table that is connected to the DimDatum table.

What do I need to add to the below DAX in order to reset on those specific dates?

CE's cumulative.  = 
CALCULATE([CE's],
    FILTER( ALLSELECTED(DimDatum),
        DimDatum[Datum] <= MAX(DimDatum[Datum])))

Herebelow example of what it looks like.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

DimDatum:

ResetDate:

You may create a measure as below.

Reset cum = 
var resetdate = 
CALCULATE(
    MAX(Reset[ResetDate]),
    FILTER(
        ALL(Reset),
        [ResetDate]<=MAX(DimDatum[Datum])
    )
)
return
CALCULATE(
    SUM(DimDatum[CE]),
    FILTER(
        ALL(DimDatum),
        [Datum]>=resetdate&&
        [Datum]<=MAX(DimDatum[Datum])
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

for each Datum, find the LASTNONBLANKVALUE of the closest Reset Date, and then add that value to the filters for your cumulation.