How to create relationships between tables with Month & Year data?

[ERing]

I currently have four disconnected tables (Web_Traffic, Phone_Cancels, Web_Cancels, Call_Center) along with a Calendar table I created.

My goal is to be able to filter each table by month and year using the Calendar table, but I'm not sure how to connect each table to my Calendar table since you can only have one relationship between each table.

Do I need to somehow concatenate the month & year in each table? If so, how do I do that?

[sergej_og · Accepted solution]

Hey @ERing ,
pls go to your PQ editor and create a new column in every table (in import mode) using following code = #date([Year], [Month], 1)
-> You should get the first of every month.
Convert the data type to type Date.
Now you should be able to connect "Date" from Calendar table to every new created column.

I guess for your DQ table you should create the field on the source side and provide this field for power bi.
Then you should be able to connect the Calendar "Date" with the Date fielf of your DQ table.

Regards

[vicky_]

Yep you're on the right track. If your date on your non-calendar tables is like dd/mm/yyyy, such as the call center table, then you can join that directly to your date column in the Calendar table. For the ones that just have month and year, create a new column in those other tables, as well as the Calendar table with those two joined together (i.e mm/yyyy).

[ERing]

Thank you

What's the best way for me to create a new column in those tables with the month & year joined together (i.e mm/yyyy)?