Manage relationships

[Nazeem]

Hi,

I have created a date table in Power BI and want to create a relartionships between mu date table and my tabler for sales data. I managed to create the date table but when I tried to create a relationship with the sales table it's not showing any data when I select YearMonth from the date tabe and Revenue from the sales table. What am I doing wrong? is it because of the format of "accrual period month" in the sales table that it is not working?

[Nazeem]

I think that the problem is that the column Accrual.period.Month is of "Data Type" text. Anyome knows hot to convert 201602 (text type) to a date type?

[Anonymous]

You could create a new column in your Accounting table which will act as key for your relationship to your date table.

DateKey = DATEVALUE(LEFT(Accounting[Accrual Period.Month],4)&"-"&RIGHT([Accrual Period.Month],2)&"-01")

This will extract the year and month from your existing column and append 01 as the first day of that month. 

Br,
Magnus

[Nazeem]

Thanks for the reply,

I get the following error message when eneterting your code:

I think it's because there is a date value in column Accrual Period:Month that is Unknown

Any clue how to solve this?

Thanks

[Anonymous · Accepted solution]

You could either filter that row(s) or replace the value with some other date to make it work. Both these options are done in the query editor before you load the table. Click on Edit Queries to open the query editor. 

[Nazeem]

Thanks, much appreciated 😄