cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Charles_Tex
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

Charles_Tex_0-1637164538549.png

 

dateday of weekday of monthmonth
01/11/20211111
02/11/20212211
03/11/20213311
04/11/20214411
05/11/20215511
06/11/20216611
07/11/20217711
08/11/20211811
09/11/20212911
10/11/202131011
11/11/202141111
12/11/202151211

 

 

  • a foreacast table (at a month granularity)

Charles_Tex_1-1637164554861.png

 

ShopCategoryMonthforecast
SHOP-1bedroomnov.-21         100 000 €
SHOP-1bedroomdec.-21           80 000 €
SHOP-1kitchennov.-21           90 000 €
SHOP-1kitchendec.-21           50 000 €
SHOP-2bedroomnov.-21           60 000 €
SHOP-2bedroomdec.-21           50 000 €
SHOP-2kitchennov.-21           20 000 €
SHOP-2kitchendec.-21             8 000 €
  • a % of increase based on the day of week.

Charles_Tex_2-1637164581666.png

 

ShopCategoryday of week% increase
SHOP-1bedroom110%
SHOP-1bedroom220%
SHOP-1bedroom35%
SHOP-1bedroom42%
SHOP-1bedroom512%
SHOP-2bedroom17%
SHOP-2bedroom26%
SHOP-2bedroom35%
SHOP-2bedroom412%
SHOP-2bedroom545%
SHOP-1Kitchen17%
SHOP-1Kitchen220%
SHOP-1Kitchen35%
SHOP-1Kitchen42%
SHOP-1Kitchen512%
SHOP-2Kitchen17%
SHOP-2Kitchen26%
SHOP-2Kitchen35%
SHOP-2Kitchen412%
SHOP-2Kitchen545%

 

this is my data model:

Charles_Tex_3-1637159948781.png

 

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:

Charles_Tex_3-1637164614343.png

 

dateday of weekmonthshopcategory% of increaseForecast
01/11/2021111SHOP-1bedroom10%         100 000
02/11/2021211SHOP-1bedroom20%         100 000
03/11/2021311SHOP-1bedroom5%         100 000
04/11/2021411SHOP-1bedroom2%         100 000
05/11/2021511SHOP-1bedroom12%         100 000
06/11/2021611SHOP-1bedroomNull         100 000
07/11/2021711SHOP-1bedroomNull         100 000
08/11/2021111SHOP-1bedroom10%         100 000
09/11/2021211SHOP-1bedroom20%         100 000
10/11/2021311SHOP-1bedroom5%         100 000
11/11/2021411SHOP-1bedroom2%         100 000
12/11/2021511SHOP-1bedroom12%         100 000
01/11/2021111SHOP-2Kitchen7%           20 000
02/11/2021211SHOP-2Kitchen6%           20 000
03/11/2021311SHOP-2Kitchen5%           20 000
04/11/2021411SHOP-2Kitchen12%           20 000
05/11/2021511SHOP-2Kitchen45%           20 000
06/11/2021611SHOP-2KitchenNull           20 000
07/11/2021711SHOP-2KitchenNull           20 000
08/11/2021111SHOP-2Kitchen7%           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
BeaBF
Super User
Super User

@Charles_Tex Hi!

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

Hello BF,

 

I've updated my previous post with the tables.

Thx for your help

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

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

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors