Help Needed - Forecast Submission Union with Real Sales

[Sojo089]

Dear community,

Hopefully, this post finds you all well! I would really appreciate help with a small problem I've got.

This is my requirement. Please find this useful information to understand the problem.

Forecast Submission: Refers to the submission made in one specific month. It shows as S.2021-09 which stands for the forecast submitted during the month of September for the following months: current month + the next 23 months.

Sales database: Refers for the sales from 2018-01 until the present month.

Table Examples.

FCST Table

Submission	SKU	2021-01	2021-02	2021-03	...
S.2021-01	1111	100	110	120	...
S.2021-02	1111	N/A	115	120	...
S.2021-03	1111	N/A	N/A	200	...
...	...	...	...	...	...

Sales Table 

SKU	2021-01	2021-02	2021-03	...
1111	90	400	50	...

Desired Goal

So basically as you can see the Forecast table is not interested in the past. My desire is to substitute the N/As in the forecast table with the historic sales data follows.

Submission	SKU	2021-01	2021-02	2021-03	...
S.2021-01	1111	100	110	120	...
S.2021-02	1111	90	115	120	...
S.2021-03	1111	90	400	200	...
...	...	...	...	...	...

And so on.

Would you guys be so kind to help me out on how to accomplish something like this?

[Ben_H · Accepted solution]

Hi @Sojo089,

Here's how I would approach it.

1. Transpose each table
2. Filter out the N/A's from FCST table
3. Where the value is N/A join onto the sales table and replace with that value.
4. Union back together and cross tab the data back into the original format.

I've attached a sample workflow.

Regards,

Ben

[Sojo089]

Dear Ben! @Ben_H

Thanks so much! Ill try it and let you know how it goes!