Forum Discussion

JBeyers's avatar
JBeyers
Advocate 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: I want to make a sum, and display it across all months. I can't do this with the "SUM" or "SUMX" function, because i...
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

     

JBeyers's avatar
JBeyers
Advocate 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
Advocate 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.

  • JBeyers's avatar
    JBeyers
    Advocate IV
    10 years ago

    Haegi Here is a small sample of the columns of interest. Do  you know what I'm missing/doing wrong?

    Daily Output   ...   Timestamps                   Sensoraddress
    0                            2-7-2015 11:25:00        0x0001

    0                            2-7-2015 11:25:00        0x0002

    100                        2-7-2015 11:25:00        0x0003

    100                        2-7-2015 11:25:00        0x0004

    100                        2-7-2015 11:25:00        0x0005

    0                            2-7-2015 11:30:00        0x0001

    200                        2-7-2015 11:30:00        0x0002

    200                        2-7-2015 11:30:00        0x0003

    200                        2-7-2015 11:30:00        0x0004

    200                        2-7-2015 11:30:00        0x0005

    200                        2-7-2015 11:35:00        0x0001

     

    • konstantinos's avatar
      konstantinos
      Memorable Member
      10 years ago

      Not sure what is not working but a long shot I suppose you have months in x-axis and a relantionship between a date table and the timestamp columnn..

      Try convert the timestamp to "Only Dates"..if you haven't since Date/time cannot relate to only dates...

      In case you need both time a date you need also a time table and split column to only dates and only time..

       

      edit - I noticed in your formula that you use the timestamp from the same table...You need to have a Date table related to your historytimestamp table...and use these dates in FILTER 

      • JBeyers's avatar
        JBeyers
        Advocate IV
        10 years ago

        I'm not entirely sure what you mean, do you mean I have to make 2 extra columns? 
        One with the date only, and one with the time only -> like this:

        Daily Output   ...   Timestamps                   Sensoraddress         Times               Dates
        0                            2-7-2015 11:25:00        0x0001                     11:25:00          2-7-2015

        0                            2-7-2015 11:25:00        0x0002                     11:25:00          2-7-2015

        100                        2-7-2015 11:25:00        0x0003                     11:25:00          2-7-2015

        100                        2-7-2015 11:25:00        0x0004                     11:25:00          2-7-2015

        100                        2-7-2015 11:25:00        0x0005                     11:25:00          2-7-2015

        0                            2-7-2015 11:30:00        0x0001                     11:30:00          2-7-2015

        200                        2-7-2015 11:30:00        0x0002                     11:30:00          2-7-2015

        200                        2-7-2015 11:30:00        0x0003                     11:30:00          2-7-2015

        200                        2-7-2015 11:30:00        0x0004                     11:30:00          2-7-2015

        200                        2-7-2015 11:30:00        0x0005                     11:30:00          2-7-2015

        200                        2-7-2015 11:35:00        0x0001                     11:35:00          2-7-2015

        I tried this but if I use this in my DAX expression this won't work:

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

        What am I doing wrong?