Rolling sum and reset negative value to 0

[Anonymous]

I am trying to reset negative rolling stock to 0 and recalculate the stock. In the screenshot below for example on date 2020/01/17 and 2020/01/21 it should be a 1. 

This si the logic I have so far. 

Stock1 = CALCULATE (

SUM ('Query1'[Workload per activity]);

FILTER( Allexcept(query1; query1[Assignment group]); [column 2] <= MAX ( [Column 2] ) && [Measure 3] = [Timestamp] && ([Workload per activity] = 1 || [Workload per activity] = -1 )

))

[mahoneypat]

Not having your data model, it was tricky to come up with a DAX expression that included your [Measure 5] measure.  So here are two versions that should help.

The first shows the approach you could take if Measure 5 was a column in your table (I called it Stock).

Rolling Since Last 1 =
VAR __thisdate =
    SELECTEDVALUE ( Stock[Date] )
VAR __latest1value =
    CALCULATE (
        MAX ( Stock[Date] ),
        ALL ( Stock[Date] ),
        Stock[Measure 5] = 1,
        Stock[Date] <= __thisdate
    )
RETURN
    CALCULATE (
        SUM ( Stock[Measure 5] ),
        ALL ( Stock[Date] ),
        Stock[Date] <= __thisdate,
        Stock[Date] >= __latest1value
    )

Here is the version that uses [Measure 5] as a measure.  You will have to test if it works for you.

Rolling Since Last 1 with Measure 5 =
VAR __thisdate =
    SELECTEDVALUE ( Stock[Date] )
VAR __latest1value =
    CALCULATE (
        MAX ( Stock[Date] ),
        ALL ( Stock[Date] ),
        FILTER ( VALUES ( Stock[Date] ), [Measure 5] = 1 ),
        Stock[Date] <= __thisdate
    )
RETURN
    CALCULATE (
        SUMX ( VALUES ( Stock[Date] ), [Measure 5] ),
        ALL ( Stock[Date] ),
        Stock[Date] <= __thisdate,
        Stock[Date] >= __latest1value
    )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi Pat,

Thank you for your repsonse. It is more like I need the rolling sum to be reset to 0 when it becomes negative. and not necessarily when measure is 1. Can this be generalised?