Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.