cancel
Showing results for
Search instead for
Did you mean:
Highlighted
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 (
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 )
))

2 REPLIES 2
Highlighted
Community Champion

## 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

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?

## Helpful resources

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors