how to calculate forecast based on previous month value in future

[PBI5851]

Hello,

I have the below two tables and date table as follows. 

TM Table

TM Name	TMID
Steve	St123
Mike	Mk234
Mitt	Mi223
John	Jo876
Dave	Da345
Bob	Bo123

GainTable

TMID	GainDate	GainCount
St123	8/1/2023	1
St123	8/5/2023	1
St123	8/7/2023	1
St123	8/10/2023	1
St123	8/15/2023	4
St123	8/6/2023	5
St123	8/8/2023	2
Mk234	8/1/2023	4
Mk234	8/5/2023	5
Mk234	8/7/2023	5
Mk234	8/10/2023	2
Mi223	8/15/2023	4
Mi223	8/6/2023	1
Mi223	8/8/2023	1
Mi223	8/1/2023	2
Mi223	8/5/2023	4
Mi223	8/7/2023	5
Jo876	8/10/2023	5
Jo876	8/15/2023	1
Jo876	8/6/2023	4
Jo876	8/8/2023	5
Jo876	8/1/2023	2
Da345	8/5/2023	4
Da345	8/7/2023	4
Da345	8/10/2023	1
Da345	8/15/2023	1
Bo123	8/6/2023	2

I also have a date table for each day for the next 5 years and a column indicating a holiday as it is needed for calculating the forecast. 

Assuming the business days for Aug is 20, sep is 19, oct 22 and nov is 16

Date Table looks like this. didnt add the entire rows as it become too big to paste here

Datetable	Holiday
8/1/2023	n
8/2/2023	n
8/3/2023	y
8/4/2023	n
8/5/2023	y
8/6/2023	y
8/7/2023	n
8/8/2023	n
8/9/2023	n
8/10/2023	n
8/11/2023	n
8/12/2023	y
8/13/2023	y
8/14/2023	n
8/15/2023	n
8/16/2023	n
8/17/2023	n
8/18/2023	y
8/19/2023	y
8/20/2023	y
8/21/2023	n
8/22/2023	n
8/23/2023	n
8/24/2023	n
8/25/2023	n
8/26/2023	y
8/27/2023	y
8/28/2023	n
8/29/2023	y
8/30/2023	n
8/31/2023	n
9/1/2023	y
9/2/2023	y
9/3/2023	y
9/4/2023	y
9/5/2023	n
9/6/2023	n
9/7/2023	n
9/8/2023	n
9/9/2023	y
9/10/2023	y
9/11/2023	n
9/12/2023	n
9/13/2023	n
9/14/2023	n
9/15/2023	n
9/16/2023	y
9/17/2023	y
9/18/2023	n
9/19/2023	n
9/20/2023	n
9/21/2023	n
9/22/2023	n
9/23/2023	y
9/24/2023	y
9/25/2023	n
9/26/2023	n
9/27/2023	n
9/28/2023	n
9/29/2023	n
9/30/2023	y
10/1/2023	y
10/2/2023	n
10/3/2023	n
10/4/2023	n
10/5/2023	n
10/6/2023	n
10/7/2023	y
10/8/2023	y
10/9/2023	n
10/10/2023	n
10/11/2023	n
10/12/2023	n
10/13/2023	n
10/14/2023	y
10/15/2023	y
10/16/2023	n
10/17/2023	n
10/18/2023	n
10/19/2023	n
10/20/2023	n
10/21/2023	y
10/22/2023	y
10/23/2023	n
10/24/2023	n
10/25/2023	n
10/26/2023	n
10/27/2023	n
10/28/2023	y
10/29/2023	y
10/30/2023	n
10/31/2023	n
11/1/2023	n
11/2/2023	n
11/3/2023	n
11/4/2023	y
11/5/2023	y
11/6/2023	n
11/7/2023	n
11/8/2023	n
11/9/2023	n
11/10/2023	n
11/11/2023	y
11/12/2023	y
11/13/2023	n
11/14/2023	n
11/15/2023	n
11/16/2023	n
11/17/2023	n
11/18/2023	y
11/19/2023	y
11/20/2023	n
11/21/2023	n
11/22/2023	y
11/23/2023	n
11/24/2023	y
11/25/2023	y
11/26/2023	y
11/27/2023	y
11/28/2023	y
11/29/2023	y
11/30/2023	y
12/1/2023	n
12/2/2023	y
12/3/2023	y
12/4/2023	n
12/5/2023	n
12/6/2023	n
12/7/2023	n
12/8/2023	n
12/9/2023	y
12/10/2023	y
12/11/2023	n
12/12/2023	n
12/13/2023	n
12/14/2023	n
12/15/2023	n
12/16/2023	y
12/17/2023	y
12/18/2023	n
12/19/2023	n
12/20/2023	n
12/21/2023	n
12/22/2023	n
12/23/2023	y
12/24/2023	y
12/25/2023	n
12/26/2023	n
12/27/2023	n
12/28/2023	y
12/29/2023	y
12/30/2023	y
12/31/2023	y

The end result expected is 

TM	Aug	Sep	Oct	Nov
Steve	15	41.5	53.95	70.135
Mike	16	43	55.9	72.67
Mitt	17	44.5	57.85	75.205
John	17	44.5	57.85	75.205
Dave	10	34	44.2	57.46
Bob	1	20.5	26.65	34.645

Issues i am running into - when i join the date from date table to gain date, i am not able to view sep oct and nov. I always have to display the current +3 months. 

The Aug counts come from the Gain table and the next months calculation are.

Steve Sep =

• If Aug value is less then 40, then sep value is 50% more than aug value + business days in sep resulting in (15*0.5)+15+19 = 41.5
• If Aug value is not less than 40, then sep value is 30% more than aug value . (15*0.3)+15
• cotinuing the oct values are dependant upon the sep value and so on. 

Any suggestions on how to accomplish this please. 

[lbendlin]

I also have a date table for each day for the next 5 years and a column indicating a holiday as it is needed for calculating the forecast. 

Please provide that table, or at least the part for the four months of your scenario.

I always have to display the current +3 months. 

To report on things that are not there you need to use disconnected tables and/or crossjoins.

[PBI5851]

@lbendlin added date table

[lbendlin]

didnt add the entire rows as it become too big to paste here

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[PBI5851]

@lbendlin sorry for the delay. I have updated the original post with the data values

[lbendlin]

I arrive at different August numbers. Please check.

[PBI5851]

@lbendlin my apologies as i was away. You are correct on aug numbers. I had my calculation wrong. please advise on how you can achieve the next 3 month numbers as forcast.

[lbendlin]

Still not clear on your computations. Anyway, here is a proposal.