Hello everyone,
I'm having a hard time trying to create a table using 3 existing tables with a many many relationship.
The 3 tables I have are:
- a date table
| date | day of week | day of month | month |
| 01/11/2021 | 1 | 1 | 11 |
| 02/11/2021 | 2 | 2 | 11 |
| 03/11/2021 | 3 | 3 | 11 |
| 04/11/2021 | 4 | 4 | 11 |
| 05/11/2021 | 5 | 5 | 11 |
| 06/11/2021 | 6 | 6 | 11 |
| 07/11/2021 | 7 | 7 | 11 |
| 08/11/2021 | 1 | 8 | 11 |
| 09/11/2021 | 2 | 9 | 11 |
| 10/11/2021 | 3 | 10 | 11 |
| 11/11/2021 | 4 | 11 | 11 |
| 12/11/2021 | 5 | 12 | 11 |
- a foreacast table (at a month granularity)
| Shop | Category | Month | forecast |
| SHOP-1 | bedroom | nov.-21 | 100 000 € |
| SHOP-1 | bedroom | dec.-21 | 80 000 € |
| SHOP-1 | kitchen | nov.-21 | 90 000 € |
| SHOP-1 | kitchen | dec.-21 | 50 000 € |
| SHOP-2 | bedroom | nov.-21 | 60 000 € |
| SHOP-2 | bedroom | dec.-21 | 50 000 € |
| SHOP-2 | kitchen | nov.-21 | 20 000 € |
| SHOP-2 | kitchen | dec.-21 | 8 000 € |
- a % of increase based on the day of week.
| Shop | Category | day of week | % increase |
| SHOP-1 | bedroom | 1 | 10% |
| SHOP-1 | bedroom | 2 | 20% |
| SHOP-1 | bedroom | 3 | 5% |
| SHOP-1 | bedroom | 4 | 2% |
| SHOP-1 | bedroom | 5 | 12% |
| SHOP-2 | bedroom | 1 | 7% |
| SHOP-2 | bedroom | 2 | 6% |
| SHOP-2 | bedroom | 3 | 5% |
| SHOP-2 | bedroom | 4 | 12% |
| SHOP-2 | bedroom | 5 | 45% |
| SHOP-1 | Kitchen | 1 | 7% |
| SHOP-1 | Kitchen | 2 | 20% |
| SHOP-1 | Kitchen | 3 | 5% |
| SHOP-1 | Kitchen | 4 | 2% |
| SHOP-1 | Kitchen | 5 | 12% |
| SHOP-2 | Kitchen | 1 | 7% |
| SHOP-2 | Kitchen | 2 | 6% |
| SHOP-2 | Kitchen | 3 | 5% |
| SHOP-2 | Kitchen | 4 | 12% |
| SHOP-2 | Kitchen | 5 | 45% |
this is my data model:
What i'm trying to acheive is a table with the forecast and % of increase for each attributs shops/Category and date. Something that looks like this:
| date | day of week | month | shop | category | % of increase | Forecast |
| 01/11/2021 | 1 | 11 | SHOP-1 | bedroom | 10% | 100 000 |
| 02/11/2021 | 2 | 11 | SHOP-1 | bedroom | 20% | 100 000 |
| 03/11/2021 | 3 | 11 | SHOP-1 | bedroom | 5% | 100 000 |
| 04/11/2021 | 4 | 11 | SHOP-1 | bedroom | 2% | 100 000 |
| 05/11/2021 | 5 | 11 | SHOP-1 | bedroom | 12% | 100 000 |
| 06/11/2021 | 6 | 11 | SHOP-1 | bedroom | Null | 100 000 |
| 07/11/2021 | 7 | 11 | SHOP-1 | bedroom | Null | 100 000 |
| 08/11/2021 | 1 | 11 | SHOP-1 | bedroom | 10% | 100 000 |
| 09/11/2021 | 2 | 11 | SHOP-1 | bedroom | 20% | 100 000 |
| 10/11/2021 | 3 | 11 | SHOP-1 | bedroom | 5% | 100 000 |
| 11/11/2021 | 4 | 11 | SHOP-1 | bedroom | 2% | 100 000 |
| 12/11/2021 | 5 | 11 | SHOP-1 | bedroom | 12% | 100 000 |
| 01/11/2021 | 1 | 11 | SHOP-2 | Kitchen | 7% | 20 000 |
| 02/11/2021 | 2 | 11 | SHOP-2 | Kitchen | 6% | 20 000 |
| 03/11/2021 | 3 | 11 | SHOP-2 | Kitchen | 5% | 20 000 |
| 04/11/2021 | 4 | 11 | SHOP-2 | Kitchen | 12% | 20 000 |
| 05/11/2021 | 5 | 11 | SHOP-2 | Kitchen | 45% | 20 000 |
| 06/11/2021 | 6 | 11 | SHOP-2 | Kitchen | Null | 20 000 |
| 07/11/2021 | 7 | 11 | SHOP-2 | Kitchen | Null | 20 000 |
| 08/11/2021 | 1 | 11 | SHOP-2 | Kitchen | 7% | 20 000 |
I've tried by using GENERATE / ADDCOLUMNS / GENERATESERIES / SELECTEDCOLUMNS but unfornately i havent found the correct formula...
Any help would be greatly appreciated,
thx,
Charles
Helpful resources
Fabric Community Update - July 2025
Find out what's new and trending in the Fabric community.
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.
