Forum Discussion
Cumulative total between start date & end date
Hi all,
I am trying to figure out the following for a while already, unsuccessfully so far.
I would like to show 3 different colums with:
- Cumulative total project budget
- Project budget for period (F.e. Q1 = 3* €3,667 for project A)
- Cumulative realized project budget (F.e. Q1 2015: Project A = €33,333 - Project H = €5,780 - Project I = €4,000
Desired result:
I have the following data:
- Project nr
- Start date
- End date
- Project budget
- Duration
- Budget per month
I have already created a table calendar (as below) to try to use the 'order in progress' method, however I cannot figure it out.
Any recommendations how to solve this?
Thanks!
HI Anonymous,
It probably required you to create some variables with summarize function to manually group records and calculate the aggregated results.
You can try to create a table visual with 'year quarter' field and the following measure formula if it works for your scenario:
Cumulative total project budget = VAR _realRange = CALCULATETABLE ( VALUES ( Calendar[Date] ), VALUES ( Calendar[YearQuarter] ) ) VAR _cumRange = CALCULATETABLE ( VALUES ( Calendar[Date] ), FILTER ( ALLSELECTED ( Calendar ), [Date] <= MAXX ( _realRange, [Date] ) ) ) VAR minDate = MINX ( _cumRange, [Date] ) VAR maxDate = MAXX ( _cumRange, [Date] ) VAR filtered = FILTER ( ALLSELECTED ( Table ), COUNTROWS ( INTERSECT ( _cumRange, CALENDAR ( [Start Date], [End Date] ) ) ) > 0 ) VAR summary = ADDCOLUMNS ( ADDCOLUMNS ( filtered, "Duration", DATEDIFF ( MAX ( minDate, [Start Date] ), MIN ( maxDate, [End Date] ), MONTH ) ), "Result", [Budget per Month] * [Duration] ) RETURN SUMX ( summary, [Result] )BTW, I found your projects contain records that not start/end with month start/end with date category ranges. If you did not use month count to calculate with the budget, how did you deal with these not completed records?
Regards,Xiaoxin Sheng
5 Replies
- amitchandak
Super User
Anonymous , Refer if this file, if it can help https://www.dropbox.com/s/yuv64v0cneseghx/value%20Split%20between%20months%20start%20end%20date.pbix?dl=0
amitchandak Thank you for the file. It definitely helped me in the right direction!
HI Anonymous,
It probably required you to create some variables with summarize function to manually group records and calculate the aggregated results.
You can try to create a table visual with 'year quarter' field and the following measure formula if it works for your scenario:
Cumulative total project budget = VAR _realRange = CALCULATETABLE ( VALUES ( Calendar[Date] ), VALUES ( Calendar[YearQuarter] ) ) VAR _cumRange = CALCULATETABLE ( VALUES ( Calendar[Date] ), FILTER ( ALLSELECTED ( Calendar ), [Date] <= MAXX ( _realRange, [Date] ) ) ) VAR minDate = MINX ( _cumRange, [Date] ) VAR maxDate = MAXX ( _cumRange, [Date] ) VAR filtered = FILTER ( ALLSELECTED ( Table ), COUNTROWS ( INTERSECT ( _cumRange, CALENDAR ( [Start Date], [End Date] ) ) ) > 0 ) VAR summary = ADDCOLUMNS ( ADDCOLUMNS ( filtered, "Duration", DATEDIFF ( MAX ( minDate, [Start Date] ), MIN ( maxDate, [End Date] ), MONTH ) ), "Result", [Budget per Month] * [Duration] ) RETURN SUMX ( summary, [Result] )BTW, I found your projects contain records that not start/end with month start/end with date category ranges. If you did not use month count to calculate with the budget, how did you deal with these not completed records?
Regards,Xiaoxin Sheng
Hi Xiaoxin,
First of all, thank you for your proposed solution. The code you provided is applicable for the project I am working on right now.
Instead of calculating cumulative budget quarterly, I want to plot the cumulative progress vs week for different company (see the table below).I managed to plot the curve using your proposed code. However, instead of 5 curves (ie 5 companies), only one curve appears.
the graph will change as per company selected.
Any suggestions on how to solve this?
Thanks.
