Forum Discussion

greenguy2012's avatar
greenguy2012
Advocate I
5 years ago

Cumulative Sum Performance

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?

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      greenguy2012
      Advocate I
      5 years ago

      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

       

       

  • daxer-almighty's avatar
    daxer-almighty
    Solution Sage
    5 years ago

    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.