Relationship with date table not working

[olivierlaloo]

Hi All, I'm fairly new to Power BI and got stuck on something that seems so easy in tutorials, but on my project, haven been spending hours finding the reason why... So, i have created a new PBI based on Data in Dynamics 365 (CRM). I have retrieved tables 'Accounts', 'Salesorders' & 'SalesOrderDetails'. My SalesOrder table contains a field 'RequestDeliveryBy' which i want to use for time intelligence. I have created a new calendar table (as shown in the different tutorials) and have linked my calendar table with the field 'RequestDeliveryBy'. Now, when i try to make a simple repport showing the amount or orders per year, this is not working... Anybody has any idea?

Date calendarRelationshipOutcome in repotRequestDeliveryBy

Note: I have tried a workaround, where i have created a new field in Salesorders & Date where i have formatted the Dates fields as 'YYYYMMDD'' and made the link on both tables to this new formatted field and then it works. However, when this link is created like this, i'm not able to use formulas such as TOTALYTD, TOTALMTD, since this new formatted field is not of type date. 

Let me know your thoughts!

[Seth_C_Bauer]

@Anonymous Can you give an example of what the issue is specifically?

[Anonymous]

Hi @Seth_C_Bauer, Sorry for late replay

I am designing a weekly sales report for last four week. By  stores and  categories. but when I am adding week number column to X-axis , I am getting blank in the report visualization.before adding week column to X-Axisafter adding week number column to X-axis

[olivierlaloo]

Hi @Seth_C_Bauer, you were right. The issue was that in the Edit Query section, the field was formatted as date + time + timezone instead of date+time.

Changing this field + apply + reload all data solved the trick.

Thanks!