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

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

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