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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

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

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors