Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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])))
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])))
@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
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 😛
@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:
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 🙂
I use the same formula, and works.
Can you take a print? or sample data? The Financial[Date] have 1 date per month?
More details can help.
I think your formula is correct, so could you please provide more information:
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
@tringuyenminh92 I dont understand why I need to create another date table when I do have one already.
Hi @Maram When using All(Original Table) you will lost the filter of another column/categories in this table. And you have to workaround by using AllExcept to fix that problem. But with seperate Dates table, it will not affect to any filter in Fact table.
But your question is so great, if I have chance to meet my idol Marco Russo, I will ask him to explain or confirm about my understand of his design in this time pattern: http://www.daxpatterns.com/time-patterns/
@Anonymous I don't think the duplicated(multiple transaction in same date) is the problem with his situation cause he is using filter ALL.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |