Forum Discussion

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

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

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

Related content