Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
I have 2 fact tables:
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
@awo,
Add to your calendar table column with quaters and create relationship beetween quaterly data abd calendar table.
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 ..
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).
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.
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.
Proud to be a Super User!
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.
1. Monthly data | ||||||||
Supplier Name | Supplier Number | Spend | Financial Year | Financial Quarter | Financial Month | Business Unit | Object Account | Index |
a | 1 | 2 257 | 15/16 | 2. Quarter | January | Balance Sheet | 1111 | 15/16January |
b | 2 | 1 437 | 15/16 | 4. Quarter | September | Polymers | 8435 | 15/16September |
n | 5 | 2 086 | 16/17 | 2. Quarter | March | Congresses | 8555 | 16/17March |
a | 1 | 968 | 16/17 | 4. Quarter | August | P&B | 8100 | 16/17August |
b | 2 | 2 042 | 15/16 | 2. Quarter | January | Trademarks | 1230 | 15/16January |
n | 5 | 831 | 14/15 | 3. Quarter | April | Patents | 8100 | 14/15April |
a | 1 | 2 100 | 16/17 | 1. Quarter | November | Procurement | 8025 | 16/17November |
b | 2 | 3 897 | 15/16 | 1. Quarter | December | Management | 8100 | 15/16December |
n | 5 | 3 380 | 16/17 | 3. Quarter | June | Canteen | 1215 | 16/17June |
a | 1 | 7 233 | 16/17 | 2. Quarter | February | Warehouse | 8144 | 16/17February |
b | 2 | 1 470 | 15/16 | 4. Quarter | July | Production support | 8019 | 15/16July |
2. Quarterly Data:
Item Number | Item UOM | Supplier Number | Supplier Name | Financial Year | Financial Quarter | Calendar Year | Calendar Quarter | Transaction Quantity |
523310990 | M | 1 | a | 14/15 | 1. Quarter | 2014 | 4. Quarter | 20 070 |
523311824 | M | 2 | b | 14/15 | 1. Quarter | 2014 | 4. Quarter | 5 950 |
523314436 | M | 5 | n | 14/15 | 1. Quarter | 2014 | 4. Quarter | 9 000 |
523314437 | M | 1 | a | 14/15 | 1. Quarter | 2014 | 4. Quarter | 7 500 |
723314442 | M | 2 | b | 14/15 | 1. Quarter | 2014 | 4. Quarter | 12 000 |
823314443 | M | 5 | n | 14/15 | 1. Quarter | 2014 | 4. Quarter | 7 400 |
923314447 | M | 1 | a | 14/15 | 1. Quarter | 2014 | 4. Quarter | 4 500 |
123314448 | M | 2 | b | 14/15 | 1. Quarter | 2014 | 4. Quarter | 6 000 |
3. piece of Calendar, all in Dax
Date | Year | MonthofYear | MonthIndex | Financial Month Order | FinMonth | Index | MonthYear | YearMonth | CalMonthName | FinancialYear | FinancialYear2 | QuarterofYear | FinancialQuarter | FinancialQuarterName | Fin Quarter and Year |
01.10.2015 | 2015 | 10 | 58 | 1 | Oct | 15/16October | Oct 2015 | 201510 | October | 2015/2016 | 15/16 | 4 | 1 | 1. Quarter | 1. Quarter 15/16 |
01.11.2015 | 2015 | 11 | 59 | 2 | Nov | 15/16November | Nov 2015 | 201511 | November | 2015/2016 | 15/16 | 4 | 1 | 1. Quarter | 1. Quarter 15/16 |
01.12.2015 | 2015 | 12 | 60 | 3 | Dec | 15/16December | Dec 2015 | 201512 | December | 2015/2016 | 15/16 | 4 | 1 | 1. Quarter | 1. Quarter 15/16 |
01.01.2016 | 2016 | 1 | 61 | 4 | Jan | 15/16January | Jan 2016 | 201601 | January | 2015/2016 | 15/16 | 1 | 2 | 2. Quarter | 2. Quarter 15/16 |
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.
Proud to be a Super User!
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |