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) volumes, by client and by year. It also contains an index field based on each client. This index is ordered by year. I am trying to calculate the running total volume of "Remaining"

 

This problem can be solved in excel with a relatively simple function and it consistently returns the expected results for Remaining. I run into issues however when I try to implement this same logic in DAX or power query. It doesn't always align with the expected results. 

 

Remaining function in excel: 

 

                 = IF(

                           IF([client_index]=1,0,RemainingPriorResult)+[DNU]-[Used]

                           <0,0, 

                           IF([client_index]=1,0,RemainingPriorResult)+[DNU]-[Used]

                         )

 

 

I have put together a DAX calculated column that returns the expected results throughout most of my dataset, however, it fails on some clients. 

Remaining_Calc = 
VAR D =
    SUMX (
        FILTER (
            sampledata,
            sampledata[client_id] = EARLIER ( sampledata[client_id] )
                && sampledata[client_index] <= EARLIER ( sampledata[client_index] )
        ),
        sampledata[DNU]
    )
VAR U =
    SUMX (
        FILTER (
            sampledata,
            sampledata[client_id] = EARLIER ( sampledata[client_id] )
                && sampledata[client_index] <= EARLIER ( sampledata[client_index] )
        ),
        sampledata[Used]
    )
RETURN
    IF((D - U)<0,0,D-U)

Below is a sample where this logic fails for all clients except #3

 

My calculation needs generate a set of results for Remaining that exactly matches the set of results generated in Excel.

See attached pbix file with sample dataset and expected results out of Excel:

https://drive.google.com/file/d/1-xekOC04kWeH_nPe4Yw1_heWwusZDa0Q/view?usp=sharing

 

Is a running total calculation with recusion like this even possible with DAX or M? 

 

Thank you for your time. 

Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Anonymous DAX can't do recursion. M can. Will have to take a closer look to see if you really require recursion. Some recursive-like problems are solvable without actual recursion.

  • 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