Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Slow cumulative running total

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 d...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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)
        )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        it did not...

         

        i see the issue is the <=MAX date statement

         

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