Forum Discussion
DAX: How to perform a cummulative summation
- 10 years ago
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
Seems like you want a cumulative total? Is that correct?
http://www.daxpatterns.com/cumulative-total/
- JBeyers10 years ago
Advocate IV
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])
)
)- Haegi10 years ago
Advocate V
Yes you formula seems to be correct :smileyhappy:
It's possible you join an sample of you're dataset perhaps ?
Regards.
- JBeyers10 years ago
Advocate IV
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 0x00010 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