Forum Discussion
Cumulative Total
Hello. I am trying to create a cumulative total for each month but I am getting data per month which I don't want to have. I used this measure
Cumulative = CALCULATE(
SUM(Finance[Column CC]),
FILTER(ALL(Finance[Date]), Finance[Date] <= MAX (Finance[Date])))
Thanks in advance.
16 Replies
I think your formula is correct, so could you please provide more information:
- Control you are using (charts/table/matrix)
- Sample data
The data is coming from each centre and is collected once every month and is split by Actual and Budget.
Centre
Date
Income
Staff
Agency
Other costs
Act/Bud
1
31/03/2015
-2000
200
200
122
Act
2
31/03/2015
-4000
100
100
100
Act
1
31/03/2015
-2000
200
200
122
Bud
2
31/03/2015
-4000
100
100
100
Bud
1
30/04/2015
-2000
200
200
122
Act
2
30/04/2015
-4000
100
100
100
Act
1
30/04/2015
-2000
200
200
122
Bud
2
30/04/2015
-4000
100
100
100
Bud
The data is mocked up but you get the general idea.
To get the Column CC I created a cal column which is Column CC = (-Finance[Income] - Finance[Staff] - Finance[Agency]- Finance[Other costs])
I am using bar graph.
I hope it is clear, if not let me know.
You need to add one more Dates table and link your date column to this date:
Refer: https://community.powerbi.com/t5/Desktop/DAX-How-to-perform-a-cummulative-summation/td-p/7378
Anonymous It is what I get as well. I would like to have cumulative actual and budget in one graph. Is there any way of doing this?
Maram It could be, just let us know how your data format of fact and budget are (same table or separate tables). I will provide suitable solution same as https://community.powerbi.com/t5/Desktop/Allocate-monthly-target-to-day/m-p/97285#M40987
