Forum Discussion
How to do running total calculation with zero out negative value from last time period
Hey there,
I'm working on one project to calculate the ending inventory.
https://app.powerbi.com/links/60g4xsKrxq?ctid=59034451-9b9d-4095-95cb-cb280da28cc6&pbi_source=linkShare
Instead of table A's way of calculation, I want to use table B, which will zero out last time ending inventory, if it's negative. In other words, it will take 0 as starting point to calculate this week ending inventory, instead of taking the last week negative ending inventory as the initial value.
Ending Inventory = Beginning Inv (last week ending Inv) + Supply - Demand
I tried the dax, but it seems that doesn't work. Would you help to take a look into that?
6 Replies
- sevenhillsSuper User
when clicked the file link: getting error:
Selected user account does not exist in tenant 'Pharmavite LLC' and cannot access the application '871c010f-5e61-4fb1-83ac-98610a7e9110' in that tenant. The account needs to be added as an external user in the tenant first. Please use a different account.
Just remove the sensitive data and have some sample data and provide the link/file here ...
- sevenhillsSuper User
I worked on manually typing using your image data:
Data table Input:Added measures: I added lot more measures to go through the data! Remove those not needed!
MEASURE 'Table1'[Day Supply] = sum(Table1[Supply]) MEASURE 'Table1'[Day Demand] = sum(Table1[Demand]) MEASURE 'Table1'[Day Balance Adjusted] = [Day Supply] - [Day Demand] MEASURE 'Table1'[CumulativeSupply] = CALCULATE(SUM('Table1'[Supply]), FILTER( ALL('Table1'), 'Table1'[Date] <= MAX('Table1'[Date]))) MEASURE 'Table1'[CumulativeDemand] = CALCULATE(SUM('Table1'[Demand]), FILTER( ALL('Table1'), 'Table1'[Date] <= MAX('Table1'[Date]))) MEASURE 'Table1'[Adjusted Beginning Balance] = VAR CurrentDate = MAX('Table1'[Date]) VAR PreviousDate = CALCULATE(MAX('Table1'[Date]), FILTER(ALL('Table1'), 'Table1'[Date] < CurrentDate)) RETURN IF( ISBLANK(PreviousDate), 0, VAR PreviousClosingBalance = SUMX( FILTER( ALL('Table1'), 'Table1'[Date] = PreviousDate), VAR _DayBeforeDate = CALCULATE(MAX('Table1'[Date]), FILTER(ALL('Table1'), 'Table1'[Date] < PreviousDate)) VAR _TempCalc1 = CALCULATE([Day Balance Adjusted], FILTER(ALL('Table1'), 'Table1'[Date] = _DayBeforeDate)) VAR PreviousBeginningBalance = IF(ISBLANK(_DayBeforeDate), 0, IF( _TempCalc1 > 0,_TempCalc1, 0) ) RETURN PreviousBeginningBalance + CALCULATE([Day Balance Adjusted], FILTER(ALL('Table1'), 'Table1'[Date] = PreviousDate)) ) RETURN IF( PreviousClosingBalance > 0, PreviousClosingBalance, 0 ) ) MEASURE 'Table1'[ClosingBalance] = [Adjusted Beginning Balance] + [Day Balance Adjusted] MEASURE 'Table1'[BeginningBalance] = -- Unadjusted Beginning Balance = var _sel = MAX( Table1[Date] ) var _calc2 = SUMX( FILTER( all('Table1'), Table1[Date] < _sel), COALESCE([Day Balance Adjusted], 0) ) return COALESCE(_calc2, 0)Sample output:
Regular matrix:Matrix ... switch values to rows:
hope this helps!
- naoyixue1Post Patron
sevenhills Thanks a lot. I will try that approach and accept that as a soltuon if it works. Again, thank you so much!
- naoyixue1Post Patron
Hey all, to make it easy I created a excel tabl here at below link.
All columns highligted in yellow are the raw data. I just added another two calculated columns there in blue, which I want to calculate in Power BI. Actually, I just want to calculate the ideal ending inventory, which is on hand + supply - demand. And then carry over currnet week ending inventory to next week as the beginning inventory, if current ending inventory is greater than 0. If it's negative, I will use 0 as the starting inventory in that week to repeat the calculation. Due to that approach, we will see the differnce. (see below example)
Item A:Week: 4/30/2025
Week: 5/7/2025
Normal Calculation:
Ending Inventory in the week of 4/30 = Last week ending invnetory (16 in the week of 4/23/2025) - demands in the week of 4/30 (37) + supply in the week of 4/30 (20) = 16 - 37 +20 = -1
Ending Inventory in the week of 5/7 = Last week ending invnetory (-1 in the week of 4/30/2025) - demands in the week of 5/7 (14) + supply in the week of 5/7 (21) = -1-14+21 = 6
Ideal Calculation:
Ending Inventory in the week of 4/30 = Last week ending invnetory (16 in the week of 4/23/2025) - demands in the week of 4/30 (37) + supply in the week of 4/30 (20) = 16 - 37 +20 = -1
Since -1 is less than 0, so I will take 0 as the beginning inventory in the week of 5/7
Ending Inventory in the week of 5/7 = Last week ending invnetory (0 in the week of 4/30/2025) - demands in the week of 5/7 (14) + supply in the week of 5/7 (21) = 0-14+21 = 7
Would you let me know how I can achieve that calculation in Power BI? Thanks a lot!