Hello! I need help with the following decisions
I have a Sales table, with a sale date, and a related Calendar table.
Now I must calculate sales for different special dates. These dates vary from year to year, and a particular date can fall into more than one type of event. For example:
| NAME | INITIAL DATE | END DATE | YEAR |
| BLACK FRIDAY CAMPAIGN | 19/11/2024 | 23/11/2024 | 2024 |
| BLACK FRIDAY CAMPAIGN | 18/11/2025 | 21/11/2025 | 2025 |
| CHRISTMAS | 15/11/2024 | 25/12/2024 | 2024 |
| CHRISTMAS | 18/11/2025 | 30/12/2025 | 2025 |
I have to create measures like sales amount, PY sales amount, and PY ALL sales amount for each of these events.
Should I relate this table to calendar by year? Or make another table that is [NAME, DATE]
If you kept only this table, how could you calculate sales for example Christmas?
If I wanted to use DATESBETWEEN I wouldn't have a unique value for INITIAL DATE and END DATE.
I welcome feedback on how best to model it!
Thank you!
Edit: Expected Result
| PRODUCT ID | SALES | CHRISTMAS SALES |
| 32445 | 1345 | 102 |
| 53625 | 3452 | 500 |
| 71832 | 2 | 0 |
| | | |
| YEAR | SALES | CHRISTMAS SALES |
| 2023 | 1789 | 165 |
| 2024 | 2710 | 437 |
| 2025 | 300 | 0 |
