Breakdown Monthly Forecast by Day based on daily weights

[fefifa]

Hi All! I'm trying to build a dashboard that shows daily forecast vs. actual sales dynamically. But we only have forecast by month. Since daily sales fluctuate based on different weekdays and holidays, i created a file that shows daily weights. Ideally, I would get my daily forecast by multiplying monthly forecast and daily weights. 

This is what my forecast look like:

Product	Date	Forecast
kc123	1/1/2021	1000
kc123	2/1/2021	1500
kc123	3/1/2021	1200
we123	1/1/2021	1300
we123	2/1/2021	1400
we123	3/1/2021	1500
jij123	1/1/2021	1200
jij123	2/1/2021	1100
jij123	3/1/2021	1300

and this is what the weights are like:

Date	Weekday	Weights
1/1/2021	Fri	3%
1/2/2021	Sat	6%
1/3/2021	Sun	4%
1/4/2021	Mon	2%
1/5/2021	Tue	3%
1/6/2021	Wed	3%
1/7/2021	Thu	3%
1/8/2021	Fri	4%
1/9/2021	Sat	5%
1/10/2021	Sun	5%

My goal is to apply the weights to my monthly forecast so that I can build a line chart that shows daily forecast and dailly sales at the same time. Also I want to see the daily forecast for each product, not just the sum. So it would look like this:

Date	Weekday	Product	Daily Forecast
1/1/2021	Fri	kc123	30
1/2/2021	Sat	kc123	60
1/3/2021	Sun	kc123	40
1/4/2021	Mon	kc123	20
1/5/2021	Tue	kc123	30
1/6/2021	Wed	kc123	30
1/7/2021	Thu	kc123	30
1/8/2021	Fri	kc123	40
1/9/2021	Sat	kc123	50
1/10/2021	Sun	kc123	50
1/1/2021	Fri	we123	39
1/2/2021	Sat	we123	78
1/3/2021	Sun	we123	52
1/4/2021	Mon	we123	26
1/5/2021	Tue	we123	39
1/6/2021	Wed	we123	39
1/7/2021	Thu	we123	39
1/8/2021	Fri	we123	52
1/9/2021	Sat	we123	65
1/10/2021	Sun	we123	65

Thank you so much in advance!

[lbendlin]

Do you have a calendar table and is your weights table linked to it? the forecast table will likely have to stay disconnected, and you can use DAX functions to tie that information in.  Alternatively you could merge both tables in Power Query (by converting your weights dates to "beginning of month" and then joining on that field).

Which approach do you prefer?

[fefifa]

Hiii! Yes I have a calendar table that links to the weights table by date. 

To convert weights date to beginning of month, are you saying something like below?

1/1/2021	Fri	1/1/2021	3%
1/2/2021	Sat	1/1/2021	6%
1/3/2021	Sun	1/1/2021	4%
1/4/2021	Mon	1/1/2021	2%
1/5/2021	Tue	1/1/2021	3%
1/6/2021	Wed	1/1/2021	3%
1/7/2021	Thu	1/1/2021	3%
1/8/2021	Fri	1/1/2021	4%
1/9/2021	Sat	1/1/2021	5%
1/10/2021	Sun	1/1/2021	5%

I would love to try both approaches if possible!

[lbendlin]

More something like that

Date	Weekday	Product	First Day of Month	Monthly Forecast	Daily Forecast
1/1/2021	Fri	kc123	1/1/2021	1000	30
1/2/2021	Sat	kc123	1/1/2021	1000	60
1/3/2021	Sun	kc123	1/1/2021	1000	40
1/4/2021	Mon	kc123	1/1/2021	1000	20
1/5/2021	Tue	kc123	1/1/2021	1000	30
1/6/2021	Wed	kc123	1/1/2021	1000	30

(You wouldn't need the last two columns as these can be pulled from the data model or be calculated.)