Forum Discussion

JBeyers's avatar
JBeyers
Icon for Advocate IV rankAdvocate IV
10 years ago
Solved

DAX: How to perform a cummulative summation

I was wondering how I should build a graph that looks like the following:

howto.JPG

I want to make a sum, and display it across all months. I can't do this with the "SUM" or "SUMX" function, because if you would spread it across all months, the summation would break apart. 

Anyone got an idea?

Need Help
  • konstantinos's avatar
    konstantinos
    10 years ago

    First you need to have a seperate date table in order to perfom date calculations...

     

    Step 1 : Create a date table -> Go to Modelling click New Table -> enter   Dates = CALENDARAUTO()..Now you have a date table call Dates..-> New Column in this table  Month = MONTH(Dates[Date])

    3.png

     

    Step 2 : Create Relantionship between your   yourtable[Dates] ( the new only dates you created ) and Dates[Date] ( the calculated table)

    2.png

     

    Step 3 : Rewrite your formula to Measure = CALCULATE(
                             SUM(rprtsolarhistorical[Daily Output]);
                             FILTER(
                                             ALL(Dates[Dates]);
                                            Dates[Date]) <= MAX(Dates[Date])
                             )
                       )

     

    Step 4: add the months field from the new Dates table and the "measure"

     

    Hope this works

     

15 Replies

    • JBeyers's avatar
      JBeyers
      Icon for Advocate IV rankAdvocate IV
      10 years ago

      Greg_Deckler Exactly!

       

      I tried the suggested formula but it just gives me the same output as when I do a normal "SUM" or "SUMX". My DAX expression seams to be right, no?

      Measure = CALCULATE(
                            SUM(rprtsolarhistorical[Daily Output]);
                            FILTER(
                                ALL(rprtsolarhistorical[Timestamps]); rprtsolarhistorical[Timestamps] <= MAX(rprtsolarhistorical[Timestamps])
                            )
                        )

      • Haegi's avatar
        Haegi
        Icon for Advocate V rankAdvocate V
        10 years ago

        Yes you formula seems to be correct :smileyhappy:

        It's possible you join an sample of you're dataset perhaps ?

         

        Regards.