Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.