Forum Discussion
DAX: How to perform a cummulative summation
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
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
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])
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 Thank you so much for the effort, it worked! :smileyhappy:
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.
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 ?
