Forum Discussion
Cumulative Total
Filtering all Finance Table (not only a column), I get the Cumulative for the month, but if you have duplicate dates, the cumulative will be the same, in both.
Cumulative = CALCULATE(
SUM(Finance[Column CC])
,FILTER(ALL(Finance), Finance[Date] <= MAX (Finance[Date])))
tringuyenminh92 Oh good that mean I am not only one who is new to DAX!
For some reason I cannot put the actual data as I get a blank with a red cross on the photo. Hence the best I can do is create mock numberical data but the structure of database is same.
Period | CC No | Service | Region | Serv Type | Income | Staff | Agency | Other costs | Year | Month | Act/Bud | Date |
2016-01 | 1 | A | East | Care Home | 20,000 | 10,000 | 2,000 | 1,000 | 2016 | Apr | Actual | 30/04/2015 |
2016-01 | 2 | B | East | Care Home | 10,000 | 7,000 | 1,000 | 2,000 | 2016 | Apr | Actual | 30/04/2015 |
2016-01 | 3 | C | East | Care Home | 15,000 | 13,000 | 3,000 | 4,000 | 2016 | Apr | Actual | 30/04/2015 |
2016-01 | 4 | D | East | Care Home | 13,000 | 11,000 | 3,000 | 1,000 | 2016 | Apr | Actual | 30/04/2015 |
2016-01 | 5 | E | East | Supported Living | 10,000 | 6,000 | 1,000 | 2,000 | 2016 | Apr | Actual | 30/04/2015 |
2016-01 | 1 | A | East | Care Home | 35,000 | 20,000 | 1,000 | 1,000 | 2016 | Apr | Budget | 30/04/2015 |
2016-01 | 2 | B | East | Care Home | 20,000 | 14,000 | 1,000 | 2,000 | 2016 | Apr | Budget | 30/04/2015 |
2016-01 | 3 | C | East | Care Home | 25,000 | 18,000 | 3,000 | 4,000 | 2016 | Apr | Budget | 30/04/2015 |
2016-01 | 4 | D | East | Care Home | 13,000 | 11,000 | 2,000 | 2,000 | 2016 | Apr | Budget | 30/04/2015 |
2016-01 | 5 | E | East | Supported Living | 19,000 | 16,000 | 1,000 | 1,500 | 2016 | Apr | Budget | 30/04/2015 |
^^This what I expected (the only issue is it is not cumulative)
Hope this explain everything, if not let me know.
Same formula for Calculated Measure of cummulative, what stopping you to have the actual & budget cummulative?
Please check my files and sample data:
- pbix: https://www.dropbox.com/s/gv88gpbglg1rt25/Trans_budget_actual_cummulative.pbix?dl=0
- data: https://www.dropbox.com/s/ye2db7kqqvrh7d1/Trans_actual_budget_cummulative.xlsx?dl=0
CC = -Transactions[InCome] -Transactions[Staff] -Transactions[Agency]-Transactions[Other costs]
I will go to sleep after this comment cause it's late here. see u tomorrow Maram