cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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. 

 

niveekiwi_1-1593765040693.png

 

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
Super User VI
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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted
Helper I
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 Community Highlights

Check out a full recap of the month!

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