How to do running total calculation with zero out negative value from last time period

[naoyixue1]

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=linkSh...

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? 

[naoyixue1]

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!

https://docs.google.com/spreadsheets/d/1eKMw9d6sxwJEusqXo0Rt2mSLcHKDZ-9L/edit?usp=sharing&ouid=10682...

[sevenhills]

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

[sevenhills]

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!

[naoyixue1]

@sevenhills I tried, but still doesn't work. I just reuploaded my file to the google drive and shared the link. Would you mind checking that for me? Thank you so much!

Example of Ending Inv Calculation.pbix

[naoyixue1]

@sevenhills  Any thoughts? Thanks!

[naoyixue1]

@sevenhills Thanks a lot. I will try that approach and accept that as a soltuon if it works. Again, thank you so much!