Optimize the DAX Running total

[Kurren]

At the moment I use the following running total calculation from this tutorial:

Sales RT :=
VAR MaxDate = MAX ( 'Date'[Date] ) -- Saves the last visible date
RETURN
    CALCULATE (
        SUM([Sales Amount]),            -- Computes sales amount
        'Date'[Date] <= MaxDate,   -- Where date is before the last visible date
        ALLSELECTED ( Date )               -- Removes any other filters from Date
    )

I have a large number of transactions. Two questions:

1. As far as I can tell, for each transaction this recomputes the sum of all transactions with the date less than or equal to the current transaction. If this is correct, then am I correct in saying that this runs in O(n^2) complexity?
2. If so, then is there a way to just add the current transaction's total onto the previous running total so that this runs in linear, O(n), complexity? The caveat is that all slicers must be respected (which is why I use ALLSELECTED above)

Any help would be greatly appreciated.

[Kurren]

Thanks @Icey . A shame that the running total must be so innefficient without any workaround.

Why don't I need ALLSELECTED?

[Icey]

Hi @Kurren ,

1. As far as I can tell, for each transaction this recomputes the sum of all transactions with the date less than or equal to the current transaction. If this is correct, then am I correct in saying that this runs in O(n^2) complexity?

Yes.

2. If so, then is there a way to just add the current transaction's total onto the previous running total so that this runs in linear, O(n), complexity? The caveat is that all slicers must be respected (which is why I use ALLSELECTED above)

No. To my knowledge, this is by design.

In addition, in your scenario, ALLSELECTED is not needed.

Sales RT :=
VAR MaxDate = MAX ( 'Date'[Date] ) -- Saves the last visible date
RETURN
    CALCULATE (
        SUM([Sales Amount]),            -- Computes sales amount
        'Date'[Date] <= MaxDate   -- Where date is before the last visible date
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.