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 Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Solution Authors