Forum Discussion
naoyixue1
1 year agoPost Patron
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 ...
sevenhills
1 year agoSuper 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
1 year agoPost Patron
sevenhills Thanks a lot. I will try that approach and accept that as a soltuon if it works. Again, thank you so much!