Slow cumulative running total

[Anonymous]

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

I believe the issue is the date filter I am using...it is checking every date in every record to see if it is on or after the max date...anything i could do to speed this up?

Running Total = CALCULATE(

    SUM('Table_1'[SUM1]),

    FILTER(

        ALLSELECTED('Table_1'[Date]),

        ISONORAFTER('Table_1'[Date], MAX('Table_1'[Date]), DESC)

    )

)

[amitchandak]

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

[Anonymous]

I see what you are tryig to do here. It makes alot of sense. But when I do this, my calculated sum is not right.

[Anonymous]

Here is what I am doing:

I created a calendar table, created relationship with the date field in the "calendar relative" calendar table to the date in Table_1

Then I use this: 

Running Total = CALCULATE(

    SUM('Table_1'[SUM1]),

    FILTER(

        ALLSELECTED('Calendar Relative'[Date]),

        ISONORAFTER('Calendar Relative'[Date], MAX('Calendar Relative'[Date]), DESC)

    )

)

[v-yiruan-msft]

Hi @Anonymous ，

May I know if the speed became faster in your scenario after you create the calendar and change the formula for the calculated column ?

Best Regards

Rena

[Anonymous]

it did not...

i see the issue is the <=MAX date statement

I have tried the ISONORBEFORE with the max date and same issue....

[d_gosbell]

---

@Anonymous wrote:

i see the issue is the <=MAX date statement

---

The MAX is not generally a big issue, you could optimise this a little by using a variable, but I'm pretty sure the MAX inside the filter predicate would get cached.

Running Total =
VAR _maxDate = MAX('Calendar Relative'[Date])
RETURN CALCULATE(

    SUM('Table_1'[SUM1]),

    FILTER(

        ALLSELECTED('Calendar Relative'[Date]),

        ISONORAFTER('Calendar Relative'[Date], _maxDate , DESC)

    )

)

Is this model in import mode or Direct Query mode? I've never really seen too many performance issues with running sums in import mode, but in Direct Query mode it would be highly dependent on the underlying relational engine storeage, indexing etc.

[Anonymous]

good suggestion, but it did not help.

This used to be a direct query, but the dax expression took over 2 minutes to run...i made it import awhile ago and its about a minute now....

[d_gosbell]

---

@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)
    )
)

[Anonymous]

Thank you for the suggestion, but neither solution helped improve the speed