Forum Discussion
Helper IIOptimize the DAX Running total
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:
- 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?
- 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.
2 Replies
- Icey
Community Support
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.
