Slow cumulative running total

---

Anonymous wrote:

This is a basic cumulative running total I am using...SUM1 is a calculated column that is a sum of 4 columns...no issues

 

 

Actually this can sometimes cause issues as calculated columns like this can often produce resulting columns that have much higher cardinality than the source columns resulting in higher memory usage and lower performance. 

 

The other issue could be the use of ALLSELECTED, this is a relatively expensive function and I don't think it's required for a typical running sum so try just swapping the ALLSELECTED for the ALL function. Or you could even try doing a SUMX and referencing the original 4 columns (if this is faster then you could remove your calculated column entirely)

 

Running Total = CALCULATE(
    SUMX('Table_1', col1  + col2 + col3 + col4),
    FILTER(
        ALL('Table_1'[Date]),
        ISONORAFTER('Table_1'[Date], MAX('Table_1'[Date]), DESC)
    )
)

To have the best of time intelligence prefer to have Date Dimension. Try formula like

 

Running Total = CALCULATE(
    SUM('Table_1'[SUM1]),
    FILTER(
        ALL('Date'),'Date'[Date]<= MAX('Date'[Date])
    )
)
Running Total = CALCULATE(
    SUM('Table_1'[SUM1]),
    FILTER(
        ALLSELECTED('Date'[Date]),ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
    )
)

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin