cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dannvar
Frequent Visitor

Cumulative total that starts over when the total is negative

I am looking for a way to get the cumulative total to show 0 when it is a negative number and then start cumulating again when the numbers are positive. The following is what I need it to do in the table. It needs to add prior period balance + ON PO + Demand to calculate On Hand (projected) but if the On Hand projected is a -ve number it needs to show 0 and then start accumulating from when the total is +ve. I am trying to create the blue highlighted column below:

 

dannvar_0-1614821138415.png

 

1 ACCEPTED SOLUTION
rfigtree
Resolver III
Resolver III

I think this works, maybe, fingers crossed.

 

All calculated columns - have broken into steps to see what is going on.

 

net = 
var AccumPO=CALCULATE(SUM(Table1[PO]),
                FILTER(ALL(Table1),Table1[period]<=EARLIER(Table1[period])))
var AccumDemand=CALCULATE(SUM(Table1[Demand]),
                FILTER(ALL(Table1),Table1[period]<=EARLIER(Table1[period])))
return AccumPO-AccumDemand+0
offset = CALCULATE(Min(Table1[net]),
         FILTER(ALL(Table1),Table1[period]<=EARLIER(Table1[period])))
SOH(projected) =Table1[net]-[offset]

rfigtree_0-1614846426785.png

 

View solution in original post

2 REPLIES 2
rfigtree
Resolver III
Resolver III

I think this works, maybe, fingers crossed.

 

All calculated columns - have broken into steps to see what is going on.

 

net = 
var AccumPO=CALCULATE(SUM(Table1[PO]),
                FILTER(ALL(Table1),Table1[period]<=EARLIER(Table1[period])))
var AccumDemand=CALCULATE(SUM(Table1[Demand]),
                FILTER(ALL(Table1),Table1[period]<=EARLIER(Table1[period])))
return AccumPO-AccumDemand+0
offset = CALCULATE(Min(Table1[net]),
         FILTER(ALL(Table1),Table1[period]<=EARLIER(Table1[period])))
SOH(projected) =Table1[net]-[offset]

rfigtree_0-1614846426785.png

 

dannvar
Frequent Visitor

Thank you this will work. I just need to apply this as a measure since I need to replicate it for multiple products. Thank you so much for your help! @rfigtree

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors