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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Dev-13
Helper I
Helper I

Model Design and Dates

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

8 REPLIES 8
jdbuchanan71
Super User
Super User

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.

@CoreyP 

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 🙂

jdbuchanan71
Super User
Super User

So do you have a W52 or is it just the weeks in the month (W01 - W05)?

Yes WK1-W52 and M1-M12

jdbuchanan71
Super User
Super User

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 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.