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 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 agoPost Patron
sevenhills Any thoughts? Thanks!