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
Microsoft Build 768x460.png

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.

May 23 2022 episode 5 with aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!