DAX: How to perform a cummulative summation

[JBeyers]

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 if you would spread it across all months, the summation would break apart. 

Anyone got an idea?

[Greg_Deckler]

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

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

[JBeyers]

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

Yes you formula seems to be correct 

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

Regards.

[JBeyers]

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

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]

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?

[konstantinos · 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])

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

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

[elliotdixon]

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

But want it to look more like this.

any help you could give would be greatly appreciated.

Cheers.

[konstantinos]

@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 ?

[elliotdixon]

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.

[Haegi]

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

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.

[Haegi]

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.

[konstantinos]

@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..

[JBeyers]

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