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])))
For DAX, i'm new too ^_^ just write down what i want to do and then searching related functions article & topics.
Maram Back to your question about actual & budget, could you please show the current sample data of actual and budget in picture/file?
And your expectation in file/picture, so I could figure out the solution.
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.