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
Aha thank you. It turns out I need to get Date from different table, my measure now look like this :
Cumulative = CALCULATE(SUM(Finance[Column CC]),
FILTER(ALL('Calendar'[Date]), 'Calendar'[Date] <=MAX('Calendar'[Date])))I only have the actual data from April to Sept, and budget data from April to March. The graph is showing actual data from April to March, even though I don't have the actual data for actual from Sept to March. Is there a way to show zero or no actual data from Oct to March?
Hi Maram,
I'm sorry but i could not understand your description, could you please explain again? And if you take a look my picture, there is no actual data in July so it's empty. That's default with no configuration.
Your data samples were helpful. The measure is working.
Thank you for your help :)