Cumulative Sum Performance

[greenguy2012]

Hi everyone!

I’m a bit new to Dax but have a lot of BI/ SQL experience and I’m stuck on a problem.

My goal is to get a cumulative sum of an existing measure, which takes the net of sum(sales) and sum(targets) in a given time period.

My dax formulas produce the right results however

#1 only shows year to date cumulative, yet it is very performant (less than 1 sec)

#2 shows over multiple years (which is really what I want), but takes almost 2 minutes to run.

(1)
Cumulative Net =
CALCULATE (
    [MeasureNet],
   
    DATESYTD(Calendar[Date])
)

(2)
Cumulative Net =
CALCULATE (
    [MeasureNet],
   
Calendar[Date] <= max (Calendar[Date])
)

I'm using a calendar table already. Can anyone help me understand if I’m missing something that impacts performance?

[daxer-almighty]

Is this model in Direct Query mode? Or is it in Import? If it's in import and you get horrible performance for the second version, then you have to show how you calculate the base measure [MeasureNet] because this is where the problem lies.

[amitchandak]

@greenguy2012 , I am assuming your formula is

Cumulative Net =
CALCULATE (
[MeasureNet],
filter( allselected(Calendar),
Calendar[Date] <= max (Calendar[Date])
))

or

Cumulative Net =
CALCULATE (
[MeasureNet],
filter( all(Calendar),
Calendar[Date] <= max (Calendar[Date])
))

that us way to do this.

What is the data volume you have

[greenguy2012]

Hi Amit,

Both my original #2 and your allselected version produce the same result on my end. Adding the allselected doesnt change anything.

I'm only dealing with a few thousand records pulling from SQL Server