Forum Discussion
naoyixue1
Post Patron
1 year agoHow 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
Super User
1 year agoI 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
Post Patron
1 year agosevenhills 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!
- naoyixue11 year ago
Post Patron
sevenhills Any thoughts? Thanks!