cancel
Showing results for
Did you mean: Helper I

## Rolling sum and reset negative value to 0

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

2 REPLIES 2
Highlighted Super User VI

## Re: Rolling sum and reset negative value to 0

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

Proud to be a Super User!

Highlighted Helper I

## Re: Rolling sum and reset negative value to 0

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?

Announcements #### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge! #### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event. Top Solution Authors
Top Kudoed Authors
Users online (771)