Forum Discussion
How to do running total calculation with zero out negative value from last time period
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 ...
- sevenhills1 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!
- naoyixue11 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!
- naoyixue11 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!