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
Microsoft Build is May 24-26. Have you registered yet?
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
The Power BI Community Show
Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.
What difference can a User Group make for you?
At the monthly call, connect with other leaders and find out how community makes your experience even better.
