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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
awo
Frequent Visitor

how to connect 2 Fact data tables: 1 monthly aggregated data, 2nd quarterly aggregated

Hi

I have 2 fact tables:

  • A. monthly data
  • B. quarterly data - here only financial quarters and years

I created (monthly) calendar table. All works fine with monthly data. However now I have no idea how can I connect quarterly fact table with calendar and then monthly data table.

Do I need another Calendar with only quarters included, and then I can connect it to both monthly and quarterly Fact tables??

 

Thank you in advance!

 

Anna

 

 

         

10 REPLIES 10
bsas
Post Patron
Post Patron

@awo,

 

Add to your calendar table column with quaters and create relationship beetween quaterly data abd calendar table.

awo
Frequent Visitor

It does not work, as fact table contains muliple rows for quarters as well as calendar table (min 3 rows, 3 separate months in the same quarter).

So I have N to N relation ..

 

@awo

 

your calendar data should be like:

 

month      quater

Jan               1

Feb              1

May             2

Dec              4

 

Relationships from this table should be 1 to N (1 for calendar and N for fact tables).

awo
Frequent Visitor

I have many years (2011 - 2020) in my calendar, so I have each quarter N times.

I dont have any record on month in quarterly fact table.

KHorseman
Community Champion
Community Champion

You don't need more than one relationship. If your calendar table has a relationship with the month column, and that calendar table also has a quarter column, you can already use the existing relationship to show the data aggregated by quarter using that second column in your calendar table.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




awo
Frequent Visitor

I have 2 different fact tables, one of them presents other data (monthly) than the other (quarterly), and just one calendar table, which includes all: months and quarters.

 

My quarterly table has no relationship with either calendar or monthly fact table, because I have no idea how to create individual key which might connect it all.

@awo

 

Please draw your tables with few raws of data.

awo
Frequent Visitor

 

1. Monthly data        
Supplier NameSupplier NumberSpendFinancial YearFinancial QuarterFinancial MonthBusiness UnitObject AccountIndex
a12 25715/162. QuarterJanuary Balance Sheet111115/16January 
b21 43715/164. QuarterSeptemberPolymers843515/16September
n52 08616/172. QuarterMarch   Congresses855516/17March   
a196816/174. QuarterAugust   P&B810016/17August   
b22 04215/162. QuarterJanuary Trademarks123015/16January 
n583114/153. QuarterApril   Patents810014/15April   
a12 10016/171. QuarterNovemberProcurement802516/17November
b23 89715/161. QuarterDecemberManagement810015/16December
n53 38016/173. QuarterJune     Canteen121516/17June     
a17 23316/172. QuarterFebruaryWarehouse814416/17February
b21 47015/164. QuarterJuly     Production support801915/16July     

 

 

2. Quarterly Data:

 

Item NumberItem UOMSupplier NumberSupplier NameFinancial YearFinancial QuarterCalendar YearCalendar QuarterTransaction Quantity
523310990M1a14/151. Quarter20144. Quarter20 070
523311824M2b14/151. Quarter20144. Quarter5 950
523314436M5n14/151. Quarter20144. Quarter9 000
523314437M1a14/151. Quarter20144. Quarter7 500
723314442M2b14/151. Quarter20144. Quarter12 000
823314443M5n14/151. Quarter20144. Quarter7 400
923314447M1a14/151. Quarter20144. Quarter4 500
123314448M2b14/151. Quarter20144. Quarter6 000

 

 3. piece of Calendar, all in Dax

 

DateYearMonthofYearMonthIndexFinancial Month OrderFinMonthIndexMonthYearYearMonthCalMonthNameFinancialYearFinancialYear2QuarterofYearFinancialQuarterFinancialQuarterNameFin Quarter and Year
01.10.2015201510581Oct15/16OctoberOct 2015201510October2015/201615/16411. Quarter1. Quarter 15/16
01.11.2015201511592Nov15/16NovemberNov 2015201511November2015/201615/16411. Quarter1. Quarter 15/16
01.12.2015201512603Dec15/16DecemberDec 2015201512December2015/201615/16411. Quarter1. Quarter 15/16
01.01.201620161614Jan15/16JanuaryJan 2016201601January2015/201615/16122. Quarter2. Quarter 15/16

 

 

KHorseman
Community Champion
Community Champion

Months and quarters should be in your calendar table, not your data tables. Your data tables should be connected through their date columns to the date column of the calendar table, and then you will be able to  use the calendar table's month or quarter columns to categorize your data.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




awo
Frequent Visitor

@KHorseman , @bsas if you could elaborate based on data I published.

Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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