Calculate cumulative growth (%) in forecast for several projects with various start and duration

[ihet]

Hi,

I have a large dataset with lots of different projects with various duration and start dates. 

Item ID	Snapshot (date)	Actual DG3 (date)	PTC(forecast)	identify initial PTC after DG3(calc. column)	MTD PTC after DG3(measure)
1110010154	01.05.2023	03.09.2021	81 141 198	1	81 141 198
1110010154	01.06.2023	03.09.2021	81 141 198	1	81 141 198
1110010154	01.07.2023	03.09.2021	81 141 198	1	81 141 198
1110010154	01.08.2023	03.09.2021	80 760 299	1	80 760 299
501002619	01.05.2023	23.06.2023	236 921 128	1
501002619	01.06.2023	23.06.2023	236 921 128	1
501002619	01.07.2023	23.06.2023	236 921 128	0	236 921 128
501002619	01.08.2023	23.06.2023	239 921 128	1	239 921 128
			1 274 868 405		320 681 427

Each project is included in dataset faily early when scope very immature and forecast is not very reliable, however as  variuos projects matures they eventually reach a decision gate (DG3), which is when project execution begins. Actual projects cost is measured at based on DG3 forecast.

I have managed to write a measure that excludes the snapshots before the DG3 date where I can vizualize the changes in forecast after DG3 over time. However how do express this cumulative percentage?

Secondly I would  like to have a measure that can be used on the entire project portfolio, which shows the growth(%) in forecast for the total project portfolio. I would only like to include the growth after DG3, so initial impact  of the first PTC snapshot after DG3 needs to be excluded.

Most of the projects extend over several years

Are there any solution for this? Help is greatly appreciated!

[ihet]

The two cells at the bottom far right at the bottom should have been merged with the cells above.

As you see I have several datapoints. The probelm is to write a measure calculates the cumulative growth over time (snapshot date) for several projects where "MTD PTC after DG3" is the value. However I somehow need to exclude the initial impact of a new "MTD PTC after DG3" per project, so that only changes after the first entry of "MTD PTC after DG3" are included in the portfolio calculation.

Hope this understandable:)

[lbendlin]

No idea what you mean by cumulative growth.

[ihet]

Hi, I see that my table is confusing. For calculation purposes snapshot entries of project B in may and june are not relevant as this prior to the DG3 date, and which is why I made the measure "MTD PTC after DG3".

Ref your table above: I would like to see the running total of MOM % for project A and B Combined. where Project B are only included from snapshot 7/1/2023 (since this is first snapshot after the projects DG3) 

1) projec

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[ihet]

Hi,

I beleieve table below fully covers sample data realted to my issue.

The expected outcome is shown in column "accumualted growht after DG3 portfolio".

Please disreagrd the "PTC forecast" column, the essential column to calculate is the "MTD PTC after DG3".

I am very sorry for my inability to explain my issue understandably. However I will try once more:

My aim is to provide a visual graph of the of how the  PTC after DG 3 date develops over time, on a portfolio level ( with many projects with different DG 3 date). It is not the PTC itself (project portfolio size) I am looking for, but a way to visualize how the projects develops after they pass the DG 3 date

[lbendlin]

I hope someone else can help you further.

[ihet]

Item ID	Snapshot (date)	Actual DG3 (date)	PTC(forecast)	MTD PTC after DG3(measure)	MOM growth portfolio	Accumulated growth after DG3 portfolio
Project A	01.05.2023	03.09.2021	81141198	81141198
Project B	01.05.2023	23.06.2023	236921128
Project A	01.06.2023	03.09.2021	81141198	81141198	0 %
Project B	01.06.2023	23.06.2023	236921128
Project A	01.07.2023	03.09.2021	81141198	75000000	-8 %	-8 %
Project B	01.07.2023	23.06.2023	236921128	236921128	-
Project A	01.08.2023	03.09.2021	70760299	70760299	-6 %	-4 %	Project a: ((70,7/81,1)-1)+Project B: ((256,9/236,9)-1)
Project B	01.08.2023	23.06.2023	256921128	256921128	8 %

[lbendlin]

Calculating a growth requires at least two data points.  I can't see that in your sample data.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...