Forum Discussion

naoyixue1's avatar
naoyixue1
Post Patron
1 year ago

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

  • 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's avatar
      sevenhills
      Super 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!

      • naoyixue1's avatar
        naoyixue1
        Post Patron

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

  • 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=106821223017782283751&rtpof=true&sd=true