Get 6 month average of ACWP to forecast the next 6 months in power bi

[Anonymous]

I have a table called Process and it contains columns as below

Period_MonthYear	EA	ACWP	PLANNED
P1 Apr-23	1100	1010	1200
P2 May-23	1223	1200	1400
P3 Jun-23	1500	1222	1800
P4 Jul-23	1670	1230	2000
P5 Aug-23	1860	1234	2200
P6 Sep-23	2055	1244	2500
P7 Oct-23
P8 Nov-23
P9 Dec-23
P10 Jan-24
P11 Feb-24

The Period_MonthYear is join / related to DateTable

I have data up to P6 Sep-23. 
I want to get the Average of ACWP for the last 6 months, then divide the average by 6 which is 6 months and the add it to EA last period that is P6 Sep-23 and to forecast EA the next 6 months.

Below is an calculation
The Forecast EA is calculated as follow 
1. get Average of 6 month ACWP which is Average ACWP =1190

2. divide Average ACWP  by 6 (6months) that is 1190/6 will give 198.33

3. Forecast EA is calculated as : get the EA last date that is P6 Sep-23 and the value is 2055 and add 198.33 and will give 2259.33

keep adding 198.33 to the last Forecast EA for the next 6 months. as shown below.

Period_MonthYear	EA	ACWP	PLANNED	Forecast EA
P1 Apr-23	1100	1010	1200
P2 May-23	1223	1200	1400
P3 Jun-23	1500	1222	1800
P4 Jul-23	1670	1230	2000
P5 Aug-23	1860	1234	2200
P6 Sep-23	2055	1244	2500	2055.00
P7 Oct-23				2259.33
P8 Nov-23				2463.67
P9 Dec-23				2668.00
P10 Jan-24				2872.33
P11 Feb-24				3076.67

As a result of the above calculation I want to achieve the below line chart based on above

How can I achieve this in power bi

[Anonymous]

@v-yohua-msft I posted message regarding what I'm facing based on your solution but my post isn't posting at all.

@v-yohua-msft Thanks for your reply. It didn't work.
I know it is my fault for not explaining properly my tables replationship
Below is the data model / relationship and dataset that is similar.

ProcessTable
Company	Reporting_Date	ProcessName	EA	ACWP	Planned
A	01/04/2023	Shredding	1668	1618	1868
B	01/04/2023	Cutting	600	550	800
C	01/04/2023	Painting	500	450	700
A	01/05/2023	Shredding	1800	1750	2000
B	01/05/2023	Cutting	700	650	900
C	01/05/2023	Painting	550	500	750
A	01/06/2023	Shredding	2000	1950	2200
B	01/06/2023	Cutting	800	750	1000
C	01/06/2023	Painting	590	540	790
A	01/07/2023	Shredding	2200	2150	2400
B	01/07/2023	Cutting	900	850	1100
C	01/07/2023	Painting	600	550	800
A	01/08/2023	Shredding	2400	2350	2600
B	01/08/2023	Cutting	1000	950	1200
C	01/08/2023	Painting	650	600	850
A	01/09/2023	Shredding	2600	2550	2800
B	01/09/2023	Cutting	1050	1000	1250
C	01/09/2023	Painting	700	650	900

Note Datetable is the normal and continuous date but the one below shows by month.

DateTable
Date	Period_MonthYear
01/04/2023	P1 Apr-23
01/05/2023	P2 May-23
01/06/2023	P3 Jun-23
01/07/2023	P4 Jul-23
01/08/2023	P5 Aug-23
01/09/2023	P6 Sep-23
01/10/2023	P7 Oct-23
01/11/2023	P8 Nov-23
01/12/2023	P9 Dec-23
01/01/2024	P10 Jan-24
01/02/2024	P11 Feb-24
01/03/2024	P12 Mar-24
01/04/2024	P1 Apr-24
01/05/2024	P2 May-24
01/06/2024	P3 Jun-24
01/07/2024	P4 Jul-24
01/08/2024	P5 Aug-24
01/09/2024	P6 Sep-24

CompayTable

Compay

A

B

C

I still want to achieve the Forecast EA as stated in my first question.
Please help me with the sample data i have typed in here.
Note- on my power bi canvas, when the line chart is displayed, there are Date Slicer (period_MonthYear) and Company Slicer 

[v-yohua-msft · Accepted solution]

Hi, @Anonymous 

First create a new measure to calculate the 6-month average of the ACWP:

Average ACWP = CALCULATE(AVERAGE('Table'[ACWP]), ALLSELECTED('Table'))

Then create an Index column in PowerQuery and create a new calculated column:

Forecast EA = 
VAR LastEA = CALCULATE(MAX('Table'[EA]), ALL('Table'))
VAR AvgACWP = [Average ACWP] / 6
RETURN 
IF('Table'[Index]>=6,SUMX(FILTER('Table','Table'[Index]<=EARLIER('Table'[Index])&&'Table'[EA]=BLANK()),AvgACWP)+LastEA
)

Put them in the view:

Regarding how to change the line to Dotted line, you can find Line in format and change the format of the Forecast EA.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.