Extrapolate average monthly Percentage to remaining months of the year

[tek01]

I'm new to power BI and I am finding it difficult to extrapolate average monthly percentage to remaining months of the year
I have a measure called '% Actual Complete'

% Actual Complete = DIVIDE(SUM(PD_Project[BCWP]), SUM(PD_Project[ProjCost_Current_Baseline_P50]))

 I have a datetable that linked to PD_Project[Date]. The Datetable contains Period_monthYear for example P1 Apr-23, P2 May-23 and so on.
My visual looks like below

What i want to achieve is to get the Average '% Actual Complete'  and extrapolate it to the end of FY which is P12 Mar-24
How can i achieve this?

[tek01]

Please does anyone knows how to resolve this

[lbendlin]

You can use the built in trend and forecast lines

or you can create your own least squares forecast line.

[tek01]

Thanks @lbendlin This is not what i was looking for. Becasue I will be adding Planned % measure to the chart and Forecast and trendline will disappear

[lbendlin]

Then roll your own.

[tek01]

Thanks @lbendlin 
You use Table 2 = GENERATESERIES(2,12) but my x-axis is Period MonthYear e.g P1 Apr-23, P2 May-23 and so on. This is coming from Date Table. So it can't be generated and won't work

[lbendlin]

You can modify it to fit your calendar table.

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

[tek01]

I can't share the sample data but i have created table called Project

Reporting_Period	Period	ProjCost_Current_Baseline_P50	BCWS	BCWP
01/04/2023	P1	£138.79	£50.00	£45.00
01/05/2023	P2	£275.40	£100.00	£70.00
01/06/2023	P3	£83.30	£10.00	£5.00
01/07/2023	P4	£57.60	£10.00	£12.00
01/08/2023	P5	£70.60	£5.00	£7.00
01/09/2023	P6	£69.55	£22.90	£8.31
01/10/2023	P7	£192.00	£3.18	£2.93
01/11/2023	P8	£138.79	£13.32	£7.20
01/12/2023	P9	£275.40	£11.80	£10.40

DateTable

Date

Year

MonthNo

Month

MonthYearNo

Month Year

Fiscal Year

FY Quarter

FY

FYID

Period

FYPeriodID

StartofMonth

Period Rank

Relative Period

Period Year

PeriodNo

FY|Period

FYear/FY Quarter

Period_MonthYear

IsCurrentDate

31/12/2023

2023

12

December

202312

Dec-2023

2023

Q3

FY 2023/24

23-24

P9

23-24|P9

01-Dec-23

21

-3

P9 23/24

9

23-24|P9

FY 2023/24 Q3

P9 Dec-23

1

30/11/2023

2023

11

November

202311

Nov-2023

2023

Q3

FY 2023/24

23-24

P8

23-24|P8

01-Nov-23

20

-4

P8 23/24

8

23-24|P8

FY 2023/24 Q3

P8 Nov-23

1

31/10/2023

2023

10

October

202310

Oct-2023

2023

Q3

FY 2023/24

23-24

P7

23-24|P7

01-Oct-23

19

-5

P7 23/24

7

23-24|P7

FY 2023/24 Q3

P7 Oct-23

1

30/09/2023

2023

9

September

202309

Sep-2023

2023

Q2

FY 2023/24

23-24

P6

23-24|P6

01-Sep-23

18

-6

P6 23/24

6

23-24|P6

FY 2023/24 Q2

P6 Sep-23

1

31/08/2023

2023

8

August

202308

Aug-2023

2023

Q2

FY 2023/24

23-24

P5

23-24|P5

01-Aug-23

17

-7

P5 23/24

5

23-24|P5

FY 2023/24 Q2

P5 Aug-23

1

31/07/2023

2023

7

July

202307

Jul-2023

2023

Q2

FY 2023/24

23-24

P4

23-24|P4

01-Jul-23

16

-8

P4 23/24

4

23-24|P4

FY 2023/24 Q2

P4 Jul-23

1

30/06/2023

2023

6

June

202306

Jun-2023

2023

Q1

FY 2023/24

23-24

P3

23-24|P3

01-Jun-23

15

-9

P3 23/24

3

23-24|P3

FY 2023/24 Q1

P3 Jun-23

1

31/05/2023

2023

5

May

202305

May-2023

2023

Q1

FY 2023/24

23-24

P2

23-24|P2

01-May-23

14

-10

P2 23/24

2

23-24|P2

FY 2023/24 Q1

P2 May-23

1

30/04/2023

2023

4

April

202304

Apr-2023

2023

Q1

FY 2023/24

23-24

P1

23-24|P1

01-Apr-23

13

-11

P1 23/24

1

23-24|P1

FY 2023/24 Q1

P1 Apr-23

1

28/04/2024

2024

4

April

202404

Apr-2024

2024

Q1

FY 2024/25

24-25

P1

24-25|P1

01-Apr-24

25

1

P1 24/25

1

24-25|P1

FY 2024/25 Q1

P1 Apr-24

0

31/03/2024

2024

3

March

202403

Mar-2024

2023

Q4

FY 2023/24

23-24

P12

23-24|P12

01-Mar-24

24

0

P12 23/24

12

23-24|P12

FY 2023/24 Q4

P12 Mar-24

0

29/02/2024

2024

2

February

202402

Feb-2024

2023

Q4

FY 2023/24

23-24

P11

23-24|P11

01-Feb-24

23

-1

P11 23/24

11

23-24|P11

FY 2023/24 Q4

P11 Feb-24

1

31/01/2024

2024

1

January

202401

Jan-2024

2023

Q4

FY 2023/24

23-24

P10

23-24|P10

01-Jan-24

22

-2

P10 23/24

10

23-24|P10

FY 2023/24 Q4

P10 Jan-24

1

Relationship is Project[Reporting_period] and DateTable[Date]

the measure is calculated as 

% Actual Complete = DIVIDE(SUM(Project[BCWP]), SUM(Project[ProjCost_Current_Baseline_P50]))

% Planned Complete = DIVIDE(SUM(Project[BCWS]), SUM(Project[ProjCost_Current_Baseline_P50]))

what i want to achieve for the extrapolating to the end of the period year is shown below

Hope this make sense. I struggle to share link but not working so i decided to use this method so that you can understand

[lbendlin]

If you want anything but a straight line then you need to employ actual forecasting models (like I showed earlier).

Your date dimension table is not wired correctly.  Needs to be 1:* single direction.

[lbendlin]

Extrapolate how?  Linearly? Bezier curve? with Seasonality?

[tek01]

Linear extrapolation to the of the year