Hi,
I am having issues calculating the cumulative sum of estimates since each project is broken down into 5 different periods. I have a column for each period that checks if the dates of a project are within a specific period. For example this is how the project dates are checked for the first project period:
DG0 Period =
VAR DG3 = 'Modning 2024 plan'[Project Period Dates] >= 'Modning 2024 plan'[DG0] && 'Modning 2024 plan'[Project Period Dates] <= 'Modning 2024 plan'[DG3 Approval-B]
VAR DG3_empty = IF(YEAR('Modning 2024 plan'[DG3 Approval-B]) = 1899 || ISBLANK('Modning 2024 plan'[Project Period Dates]), 'Modning 2024 plan'[Modning Finished-B],'Modning 2024 plan'[DG3 Approval-B])
VAR DG3_test = DG3_empty >= 'Modning 2024 plan'[DG0] && 'Modning 2024 plan'[Project Period Dates] <= DG3_empty
return
IF(DG3_test,
1,
0)
The following periods are checked using this formula replacing the variable with the relevant period:
IF(
'Modning 2024 plan'[Project Period Dates] >= 'Modning 2024 plan'[DG3 Approval-B] && 'Modning 2024 plan'[Project Period Dates] <= 'Modning 2024 plan'[Installation start-B],
1,
0)
This check gives the following columns:
Afterwards I created measures to calculate how much of the total budget is spent in each phase:
Sum Forventet Totalkost DG0 = CALCULATE(SUMX(SUMMARIZE('Modning 2024 plan','Modning 2024 plan'[Project ID],'Modning 2024 plan'[Forventet Totalkost DG0]),'Modning 2024 plan'[Forventet Totalkost DG0]),'Modning 2024 plan'[DG0 Period] = 1,USERELATIONSHIP('Modning 2024 plan'[Project Period Dates],'Calendar'[Date])
)
Sum Forventet Totalkost DG3 = CALCULATE(SUMX(SUMMARIZE('Modning 2024 plan','Modning 2024 plan'[Project ID],'Modning 2024 plan'[Forventet Totalkost DG3]),'Modning 2024 plan'[Forventet Totalkost DG3]),USERELATIONSHIP('Modning 2024 plan'[Project Period Dates],'Calendar'[Date]), 'Modning 2024 plan'[DG3 Period] = 1)
(same formula up until the close-out period)
Next, I take this measure and calculate the rolling values for each project phase with a new measure:
Sum Forventet Totalkost DG0 rolling = IF(ISBLANK([Sum Forventet Totalkost DG0]),
BLANK(),
CALCULATE(SUM('Modning 2024 plan'[Forventet Totalkost DG0]),SUMMARIZE('Modning 2024 plan','Modning 2024 plan'[Project ID], 'Modning 2024 plan'[Forventet Totalkost DG0]),USERELATIONSHIP('Modning 2024 plan'[Project Period Dates],'Calendar'[Date]), 'Calendar'[Date] <= MAX('Calendar'[Date]), 'Modning 2024 plan'[DG0 Period] = 1))
Finally I create one final measure to summarize all of the project period rolling measures:
Sum of B forventet Totalkost for all Project Periods rolling = [Sum Forventet Totalkost DG0 rolling] + [Sum Forventet Totalkost DG3 rolling] + [Sum Forventet Totalkost Installation Start rolling] + [Sum Forventet Totalkost DG4 rolling] + [Sum Forventet Totalkost Close out rolling]
Here is the result illustrated in a table for one project:
The rolling sum is calculated correctly for each project period, however the total calculation resets at each project period. In the example, 2024 Jan should be added to 2024 Feb and continue summarizing each month with the last month, however it restarts the sum calculation at each project period.
Is there any way to get the cumulative sum of all project periods together using the measures I have? What is the best approach to take when calculating the cumulative sum of estimates for a project that is split into multiple project periods?
