Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Running total with recursion

Hello, I am trying to calculate a running total that references the calculated result of the prior row, of the same column.   For background: I have a dataset with "Used" and "DNU" (did not use) ...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

Anonymous Probably can't do this without true recursion. This is as close as I got:

Remaining = 
    VAR __Source = ALL('sampledata')
    VAR __Client = MAX('sampledata'[client_id])
    VAR __Index = MAX('sampledata'[Client_Index])
    VAR __Table = 
        ADDCOLUMNS(
            FILTER( __Source, [client_id] = __Client && [Client_Index] < __Index),
            "__Unused", [DNU] - [Used]
        )
    VAR __Table1 = FILTER( __Table, [__Unused] > 0 )
    VAR __PrevValue = SUMX( FILTER( __Table, [__Unused] > 0 ), [__Unused])
    VAR __Used = MAX('sampledata'[Used])
    VAR __DNU = MAX('sampledata'[DNU])
    VAR __Value = __DNU - __Used + __PrevValue
    VAR __Result = IF( __Value < 0, 0, __Value)
RETURN
    __Result

See this article, it *may* help. (2) Previous Value (“Recursion”) in DAX - Microsoft Power BI Community