Forecasting: Extrapolating relative to other curves

[Palmtop]

Hello All,

I've got an interesting challenge today.  I have the monthly sales data after a marketing campaign below in a table.  Alongside, the predicted low/base/high cases from a scenario analysis (red/blue/green respectively) and a baseline (no campaign, in purple).

The historical sales data obviously stops on October 2022 (shaded area).  

What I'd like is to reproduce the illustrated example done in Excel.  I want to create a measure/calculated columns to forecast the historical sales curve (black line).  It would find the latest monthly historical sales, determine where it is situated relative to the low/base/high cases, and extrapolate using that ratio.  Issue is there are lots of campaigns/products, some would be between low and base case, others would be above high case, others below baseline...etc.  

In Excel, my process is roughly as follows:

Categorise each campaign under following 5 scenarios:

1. Below baseline
2. Between baseline/low case
3. Between low/base case
4. Between base/high case
5. Above high case

Then determine where last monthly sales number is situated.  So in this case, it would be roughly 50% of the way between base/high case.  Then extrapolate using that ratio so that the curve always stays 50% of the way between the base/high case.  

The formula in this case would be: 

I've included a dummy table below.

Date	Sales	Baseline	Low Scenario	Base Scenario	High Scenario
01/02/2022	658	169	357	605	1266
01/03/2022	1079	168	353	594	1218
01/04/2022	936	167	349	582	1173
01/05/2022	725	166	345	572	1132
01/06/2022	772	165	341	561	1093
01/07/2022	716	164	337	551	1056
01/08/2022	761	163	333	541	1022
01/09/2022	767	163	330	532	990
01/10/2022	745	162	326	523	960
01/11/2022		161	323	514	931
01/12/2022		160	320	506	904
01/01/2023		159	316	497	878
01/02/2023		159	313	489	854
01/03/2023		158	310	482	831
01/04/2023		157	307	474	809
01/05/2023		156	304	467	789
01/06/2023		155	301	460	769
01/07/2023		155	298	453	750
01/08/2023		154	295	446	732
01/09/2023		153	292	440	715
01/10/2023		152	289	433	699
01/11/2023		152	286	427	683
01/12/2023		151	284	421	668
01/01/2024		150	281	416	654
01/02/2024		150	279	410	640
01/03/2024		149	276	404	627
01/04/2024		148	274	399	614
01/05/2024		147	271	394	602
01/06/2024		147	269	389	590
01/07/2024		146	266	384	579
01/08/2024		145	264	379	568
01/09/2024		145	262	374	557
01/10/2024		144	259	370	547
01/11/2024		143	257	365	537
01/12/2024		143	255	361	528
01/01/2025		142	253	356	519
01/02/2025		142	251	352	510
01/03/2025		141	249	348	501
01/04/2025		140	247	344	493
01/05/2025		140	245	340	485
01/06/2025		139	243	336	477
01/07/2025		138	241	333	470
01/08/2025		138	239	329	462
01/09/2025		137	237	325	455
01/10/2025		137	235	322	448
01/11/2025		136	233	318	442
01/12/2025		135	231	315	435

Any help or tips is much appreciated, thank you!

[Anonymous]

Hi @Palmtop ,

Power BI Desktop has a built-in prediction curve feature that you can refer to.

Forecasting in Power BI. A visual step-by-step guide to… | by Ednalyn C. De Dios | Towards Data Scie...

BTW, if you are familiar with R or Python script. You can also create R/python visuals to coding formula invoke their forecast libraries to get the forecast values based on current table records.

Time Series Analysis using R – forecast package 

Forecast Table - R script - Microsoft Power BI Community

Protection des données comment se protéger (datamic.net)

Best Regards,

Stephen Tao

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

[Palmtop]

Hello Stephen,

Thanks for the links.  I've given the built-in forecast feature a spin but it's not exactly what I'm looking for.  

Looks like a python script might be the way to go for now if I can't get a DAX measure to work.  

Thanks again!