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])))
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
Hi tringuyenminh92 Thanks for the reply :) I am new to DAX so how would you use it? I did separate the table into actual and budget before, but it doesnt really work out in the long term as I want to use the same data to create new graphs. PS its she :P
Anonymous Everything is in one table. It would be great if you could find a solution! If there is anything you need, do let me know. I appreciate it :)
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