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?
You can use the built in trend and forecast lines
or you can create your own least squares forecast line.
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
Helpful resources
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
Power BI Monthly Update - October 2025
Check out the October 2025 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
