Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi I have a data model that has a date table and fact tables. I am having issues with the model design. The date table needs to relate to the fact table by year and month, only this is not possible due to only allowing one relationship. Using one relationship will not work. Does anyone have advice on how to link the tables together? The fact table has year and month number and I wanted to link this into the date table so I can get the data by financial year. Thanks
I would add a table to your model that you could use as a lookup. It would contain the YMW values and the date you want associted to it. You would link it to your fact table that has the YMW and pull over the date field then link the date field to the Dates table.
YMW | Date |
2022M01W01 | 1/1/2022 |
2022M01W02 | 1/2/2022 |
2022M01W03 | 1/9/2022 |
2022M01W04 | 1/16/2022 |
2022M01W05 | 1/23/2022 |
2022M01W06 | 1/30/2022 |
2022M02W06 | 2/1/2022 |
2022M02W07 | 2/6/2022 |
2022M02W08 | 2/13/2022 |
2022M02W09 | 2/20/2022 |
2022M02W10 | 2/27/2022 |
2022M03W10 | 3/1/2022 |
2022M03W11 | 3/6/2022 |
2022M03W12 | 3/13/2022 |
2022M03W13 | 3/20/2022 |
2022M03W14 | 3/27/2022 |
2022M04W14 | 4/1/2022 |
2022M04W15 | 4/3/2022 |
2022M04W16 | 4/10/2022 |
2022M04W17 | 4/17/2022 |
2022M04W18 | 4/24/2022 |
2022M05W19 | 5/1/2022 |
2022M05W20 | 5/8/2022 |
2022M05W21 | 5/15/2022 |
2022M05W22 | 5/22/2022 |
2022M05W23 | 5/29/2022 |
2022M06W23 | 6/1/2022 |
2022M06W24 | 6/5/2022 |
2022M06W25 | 6/12/2022 |
2022M06W26 | 6/19/2022 |
2022M06W27 | 6/26/2022 |
2022M07W27 | 7/1/2022 |
2022M07W28 | 7/3/2022 |
2022M07W29 | 7/10/2022 |
2022M07W30 | 7/17/2022 |
2022M07W31 | 7/24/2022 |
2022M07W32 | 7/31/2022 |
2022M08W32 | 8/1/2022 |
2022M08W33 | 8/7/2022 |
2022M08W34 | 8/14/2022 |
2022M08W35 | 8/21/2022 |
2022M08W36 | 8/28/2022 |
2022M09W36 | 9/1/2022 |
2022M09W37 | 9/4/2022 |
2022M09W38 | 9/11/2022 |
2022M09W39 | 9/18/2022 |
2022M09W40 | 9/25/2022 |
2022M10W40 | 10/1/2022 |
2022M10W41 | 10/2/2022 |
2022M10W42 | 10/9/2022 |
2022M10W43 | 10/16/2022 |
2022M10W44 | 10/23/2022 |
2022M10W45 | 10/30/2022 |
2022M11W45 | 11/1/2022 |
2022M11W46 | 11/6/2022 |
2022M11W47 | 11/13/2022 |
2022M11W48 | 11/20/2022 |
2022M11W49 | 11/27/2022 |
2022M12W49 | 12/1/2022 |
2022M12W50 | 12/4/2022 |
2022M12W51 | 12/11/2022 |
2022M12W52 | 12/18/2022 |
2022M12W53 | 12/25/2022 |
I agree with @jdbuchanan71 . However, I think you could also just do this with one date table. You could create a calculated column in your date dimension to reproduce the format of your period key in your fact table by using combinations of functions like YEAR, MONTH, WEEKNUM, and FORMAT. Then join the two on that key.
If you did that, add the YMW column to your date table, your join to your fact table would be many to many unless I am not understanding your post.
Oh jeez. You are 100% correct. I completely overlooked that. Good call! Thank you 🙂
So do you have a W52 or is it just the weeks in the month (W01 - W05)?
Yes WK1-W52 and M1-M12
You would add a date field to your fact table that is the first day of the month for the year / month. For example, if your year month is 2023 October, add a date field to the fact table that is 10/1/2023. Then you can link that to your date table.
Thank you - yes this would work.
I do have one issue that would cause a problem with this though.
The data is set up in date periods in the same column so it contains both week and month data. The date period key is 201801W01 or 201901M01. W01 is week 1 of the year and M01 is first month of the year. I can do the calculation easily with the month, but I don't know how I would achieve this with the week number. I have split the month data and week data into different tables in the model so the month ones are working fine now and linking to the date table. I don't know how to achieve the same for the week column
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
108 | |
107 | |
92 | |
68 |
User | Count |
---|---|
167 | |
130 | |
129 | |
95 | |
91 |