cancel
Showing results for
Did you mean:
Frequent Visitor

## generate table using 3 existing tables - relation many many

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

2 REPLIES 2
Super User

@Charles_Tex Hi!

Can you paste the data of the 3 tables and not like images?

Frequent Visitor

Hello BF,

I've updated my previous post with the tables.

Announcements

#### The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.