cancel
Showing results for
Did you mean:
Helper III

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

Anyone got an idea?

1 ACCEPTED SOLUTION
Memorable Member

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

Konstantinos Ioannou
15 REPLIES 15
Super User IV

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

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

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

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

Proud to be a Super User!

Helper III

@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

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

Regards.

Helper III

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

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

Memorable Member

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
Helper III

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?

Memorable Member

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

Konstantinos Ioannou
Responsive Resident

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.

Cheers.

Memorable Member

@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
Responsive Resident

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.

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.

Memorable Member

@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
Helper III

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

Announcements

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