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!

[c60918]

I read through this post thinking it would solve my issue, as I thought I had an issue with the Power Query Editor date format and cannot for the life of me find the problem.  Every time I try to merge or create a relationship with the Calendar (dimension) table to any other fact table (in this case Hire YTD) it comes back with no matches.  The dates in the Calendar (dimension) table go from 7/16/11 to 2/15/29, so my fact table with 2022 dates shouldn't have a problem finding a match.  I don't understand what is going on here.  Thanks in advance.  If you respond, @Seth_C_Bauer, I will be ecstatic! I love your podcast with Mike and Tommy, excellent stuff, wish I could be at your level already:)Hire YTD - Date FormatCalendar - Date FormatJoin - Calendar to Hire YTD Merge - no Hire YTD data

[Anonymous]

Hello, as some people I am quite new in the use of Power Bi (since March 2018) and got stucked with a relationship between two data tables.

I am pulling information from ms excel and from my point of view have a linear relationship, however I am not able to get the information at detail, once I try to get the data I get the total result in each line instead the detail sum of that particular data.

I hope you guys can help me, I solve this by feeding the main table with lots of vlookups and now I am able to retrieve the information as I wanted, however I thought relationships are equivalent to vlookups.

Thank you

[Anonymous]

Hello, as some people I am quite new in the use of Power Bi (since March 2018) and got stucked with a relationship between two data tables.

I am pulling information from ms excel and from my point of view have a linear relationship, however I am not able to get the information at detail, once I try to get the data I get the total result in each line instead the detail sum of that particular data.

I hope you guys can help me, I solve this by feeding the main table with lots of vlookups and now I am able to retrieve the information as I wanted, however I thought relationships are equivalent to vlookups.

Thank you

[AlanBaker]

Hi

I have what appears to be the same issue, I have Created a Calendar Table with a Unique ID which I am using to perform Date Intelligence in order to determine planned costs for the previous week and comparing the result to the current week. You will see two tables below the first selects the fields I require BUT uses the Calendar Table "Date" field this appears to be working AOK.

The SECOND Table mirrors the first table EXCEPT that I have removed the Calendar "Date" field and substituted the "Week Begining" field from my data table and as you can see the dates between the two tables do NOT appear to be linking as the reults for the previous week are NOT populated?

I have read the helpful advice in the replies to this original lost below and I believe that the imported table "Week Begining" field is formatted as Date d/mm/yyyy within the PBI Query Editor. 

  Query EditorThe Data View of the Calendar table creation

The Calendar table which is created within the PBI Data View is formatted again as Date. I have Applied the Queries and Refreshed the graphics and data tables (several times 🙂

The Calendar Table is Linked as a ONE from the "Date" Column to the MANY column 'WeekBegining" field in the data table.

Help to solve this would be greatly appreciated....

[Seth_C_Bauer · Accepted solution]

@olivierlaloo Check to see whether or not the data type is date in the "Edit Query" section. I've run into this before and wasted a bunch of time because I didn't check there. My guess, is that it is still text in that section. (Load vs. Model, they both need to have the same datatypes for some reason).

[basel777]

This is the right answer! Thanks!

[toddfetcho]

This is not the issue as both are selected as dates in query editor.  My table only seems to correlate to one specific date and ignores all the others.

[crwinchester]

I attempted to change the data type in the query from the Transform Data window, but PowerBI still fails to correlate correctly with the calendar table. Do you have any other ideas on how to fix this? Is this something where the file or query is corrupted for some reason and the process has to be started over? Thanks.

[ecalzavara]

FFSk, many and MANY hours spent trying to understand what was the problem as all the data looked right, and BI was just "masking" the issue... I can't believe it!!! Thanks!

[markleghrand]

Thank you for this input. This has been really helpful. 

[Adebimpe]

My data type is showing as date but whenever itry to right my dax, my start date doesnt come up

[Suetam016]

I'm so glad I googled it after only 10 minutes of banging my head against the wall.
THANK YOU for saving me hours, cheers

[KarinaUrtazu]

Woooow !!!! I've been trying to solve this problem for days, I've looked at all the videos I found, on all the sites, I've done a thousand tests and nothing... And finally here is the solution. You are extremely supportive!!!! 

[Jsummers]

This has just saved me from pulling my hair out after hours of wondering why some dates were showing as 'blank', but had dates in the fields! 

[sfmike99]

Add another appreciation for this tip!

[Anonymous]

God bless you, you saved my day 😄 😄

I almost wasted 2 hours figuring out why the date join is not working.. it was the datatype difference in Edit Query section..Thanks!

[NewToPwrBi]

Yes!

Finally it worked, why.... oh why.... is it like this....

Thanks!! 🙂

[Anonymous]

@Seth_C_Bauer This worked like a charm. The data type needs to be set as "Date" in the Query Editor, just changing in the loaded data model does not work. Thanks!

[Anonymous]

Hi @Seth_C_Bauer, I am facing same problem. I have created date table using DAX in Power BI desktop. As I don't have date table in my source database. I have checked other dates types like order date and invoice date they match in data type both in Power query and report window. Can you please advice me what else can be problem???

Thanks in advance!!

[Seth_C_Bauer]

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