cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JBeyers
Helper III
Helper III

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?

1 ACCEPTED SOLUTION

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

 

Konstantinos Ioannou

View solution in original post

15 REPLIES 15
Greg_Deckler
Super User IV
Super User IV

Seems like you want a cumulative total? Is that correct?

http://www.daxpatterns.com/cumulative-total/

 

 

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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])
                      )
                  )

Yes you formula seems to be correct Smiley Happy

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

 

Regards.

@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

 

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 

Konstantinos Ioannou

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?

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

 

Konstantinos Ioannou

View solution in original post

HI @konstantinos looks like you are right on it. I had also used the great answer for cumulative totals at Dax Patterns http://www.daxpatterns.com/cumulative-total/

 

But I need to have my cumulative running over the same year. Have each year overlaying the others. To do this I figure I need a date column that calcuates what day of the year it is (ignoring the actual year) rather than an actual date

At the moment I am getting this

Seasons.jpg

 

But want it to look more like this.

Season 2.jpg

 

any help you could give would be greatly appreciated.

Cheers.

@elliotdixon not sure I understand..Sorry..do you need to compare all years in one graph?  like current year - previous  etc..or you need a lifetime value..What you would have in axis - months ?

 

 

Konstantinos Ioannou

Hi @konstantinos Cheers - I want to show all the years on the same graph. One line for each different year. The axis is our season (just a financial year - 1st July to 30th June)

 

Cheers.

 

@elliotdixon

 

Hi, I faced the same case.

First i suppose you hae a Date table with column Date (complete), Year, Month etc..

I created new measure column like this

 

CumulativeYear = 
CALCULATE(
SUM('Aggregate'[Amount]); 
FILTER(ALL(Dates[Year]); Dates[Year] = MAX(Dates[Year]));  
FILTER(ALL(Dates[Month]); Dates[Month] <= MAX(Dates[Month])))

 

I use two filter to specify a particular context: for each year you parse all month.

elliotdixon
Responsive Resident
Responsive Resident

Hi @Haegi

Thanks for the help. I can create the measure with no errors but cannot then bring it into any table or graph??

Get an error
"MdxScript(Model) (2, 43) Calculation error in measure 'InvoiceDetail'[CumulativeYear]: The function MAX takes an argument that evaluates to numbers or dates and cannot work with values of type String."

My code is.

CumulativeYear = CALCULATE(SUM(InvoiceDetail[DETAIL_KMS_TOCHARGE]), 
FILTER(ALL(Dates[Year]),Dates[Year] = MAX(Dates[Year])),  
FILTER(ALL(Dates[MonthName]),Dates[MonthName] <= MAX(Dates[MonthName])))

Same result if I remove out the Month filters and just have one on the years.

Good job!

 

This error explain that MAX function doesnt work with String value.

I suppose the column 'MonthName' is a string like "January" or "Febuary", we must instead use integer value like 1,2,3,4,5 etc..

for the Month column.

 

Regards.

@elliotdixon An approach ( maybe not the best one - but I don't work much with fiscal dates ) maybe

 

YTD Current Fiscal = TOTALYTD(SUM(Sales[Amount);'Dates'[Date];"6-30")   - The last one is for specifying the end of year ( Fiscal )

Previous YTD Fiscal = CALCULATE([YTD Current Fiscal];DATEADD('Dates'[Date];-1;YEAR))

2 Years Before YTD =  CALCULATE([YTD Current Fiscal];DATEADD('Dates'[Date];-2;YEAR))

 

This way you can have any fiscal year dates in axis and compare all of them..Also can slice on Years

axis = fiscal year 2015  will show also 2014 & 2013 

axis = fiscal year 2013  will show also 2012 & 2011  

 

Hope it works..

Konstantinos Ioannou

@konstantinos Thank you so much for the effort, it worked! Smiley Happy

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors