Linking calendar table and my own table by Month and Year column

[lukaspowerbi]

I want to create a relationship between two tables, MonthTable and FY18 Table. 

I created MonthTable below with a Year-Month column that has unique values:

Now, I would like to create a relationship with FY18 table which is a little bit tricky. The FY18 table is brought into power bi in multiple .xlsx files, each file represents one month and I created a conditional column based on its source name.

I basically need to create a column based on source name with the same format that I have in my MonthTable.

I already created a conditional Year Month Column but it's not the right format. Can anyone help? Thank you.

[v-cherch-msft]

Hi @lukaspowerbi

You may try to link the two table between the New Year Month column and Year-Month column. Here is the document for you.

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

Regards,

Cherie

[lukaspowerbi]

Thanks, that's what I tried but for some reason doesn't work. I have One to Many Relatonship

My MonthTable with unique values in a Year-Month column:

My FY18 Table with Year Month column

Both columns are text, I tried Data type too but didn't work. 

[Greg_Deckler]

When you say it doesn't work, can you be more specific? It doesn't allow you to create the relationship? You aren't getting the correct values in some visual?

[Greg_Deckler]

You probably need to specify that your Year Month column is Text in your second table. 

[lukaspowerbi]

It doesn't seem to work. It allowed me to create 1 to many relationship but tables don't communicate at all. 

Setting up both columns allowed me to create it but it won't work in my slicer.

[Greg_Deckler]

Create a new column with the formula:

New Year Month = YEAR([Year Month]) & " " & MONTH([Year Month])

Use that to link your tables.

[lukaspowerbi]

I created the column but it didn't help. I think the issue is wth my FY18 table not the calendar. 

[Greg_Deckler]

Can you supply source data that can be copied and pasted or a PBIX file? This should be pretty straight-forward.

[lukaspowerbi]

I think it's my FY18 and creating that conditional column based on Source.Name column.

Here is the file:

https://www.dropbox.com/s/82fvcvby74gc3n3/Trend%20Test.pbix?dl=0

I appriciate your help!