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
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....

      • d_gosbell's avatar
        d_gosbell
        Icon for Super User rankSuper User
        6 years ago
        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.