Hi All,
We have a scenario wherein a company has on-boarded other businesses and all the details (includng the acquired month) are available in Subsidiary master dimension table. Sample data below...
Subsidiary Id | Subsidiary Name | Acquisition Month |
1 | Business 1 | Feb-21 |
2 | Business 2 | Dec-19 |
3 | Business 3 | Oct-18 |
We have Sales transaction tables historic and current (with same structure)
Subsidiary Id | Sales Month | Sales (Historic) |
1 | Jan-20 | 100 |
1 | Feb-20 | 200 |
1 | Mar-20 | 120 |
2 | Sep-19 | 100 |
2 | Aug-19 | 300 |
2 | Nov-19 | 120 |
3 | Jul-18 | 100 |
3 | Apr-18 | 100 |
3 | Sep-18 | 120 |
Subsidiary Id | Sales Month | Sales (Current) |
1 | Jan-22 | 100 |
1 | Feb-22 | 200 |
1 | Mar-22 | 120 |
2 | Jan-22 | 100 |
2 | Feb-22 | 300 |
2 | Mar-22 | 120 |
3 | Jan-22 | 100 |
3 | Feb-22 | 100 |
3 | Mar-22 | 120 |
Requirement is to calculate the last 12 months Sales (Historic) data (for each subsidiary) based on the "acquisition month" in the subsidiary master.
expected sample output..
Subsidiary Id | Last 12 Months Sales Historic |
1 | 420 (100 + 200 + 120) |
2 | 520 (100 + 300 + 120) |
3 | 320 (100 + 100 + 120) |
the sample data has been provided only for 3 months for ease of understanding.
Any help would be highly appreciated.
Thanks.
