Inactive relationship Date vs Create_Date

[Anonymous]

Hi All, 

I have two tables:

• Date
• Opportunity

The Opportunity table contains many different date columns, including:

• Close Date (active relationship with Date Table)
• Created Date (inactive relationship with Date table)

My values weren't showing right hence I asked a question. Here is the link to the earlier question where I have asked about the DAX function is not working with inactive relationship and values were not showing correct.  -https://community.powerbi.com/t5/Desktop/Multiple-dates-in-one-table-vs-Date-Table/m-p/2398360#M860503

So I tried to create a chart witht just date to see where it is breaking. Please see the below screenshot - 

You can see, Date table is connected with Close date is perfactly fine but not connect with Created_date. 

Can anyone please help me? This is really priority report for me. My original ask was in the earlier question but I guess because of this mapping, my values aren't showing right. 

Appreciate your help. 

[jdbuchanan71 · Accepted solution]

Thank you for sharing.  The problem is, your Created_Date has a time stamp on it so there are no matching rows in the 'Date'[Date] column.  

When you get it from the direct query source do a CONVERT(Date, Created_Date) on it and it should work.  That will drop the time portion.

[jdbuchanan71]

You might need to update your direct query code to pull the new column from the view.

[jdbuchanan71]

Not in direct query mode, no.  You have to return the data exactly how you need it for it to work in the model.

[Anonymous]

@jdbuchanan71  - Hi there, another quick question. I am using DirectQuery and I added another column to my view with just date and remove the timestamp. However, I am not able to see that in PoweBI desktop. 

[Anonymous]

@jdbuchanan71  - noted. thanks a ton. Appreciate your help.

[jdbuchanan71]

I made create date the active relationship to test the file.  The problem needs to be fixed on the direct query side.   You need to change your query to only return the DATE portion of the CREATED_DATE.

[Anonymous · Accepted solution]

Hi @jdbuchanan71  - Thank you for sharing the file. However, it did not work. You can see the months are not showing plus you made create_date to active relationship. 

This is how I want to see in the table- 

Date (from Date table)	Revenue by Close date	Revenue by Create_date
3/5/2022	$100	$200
3/1/2022	$500	$190
12/25/2021	$1000	$2000

[jdbuchanan71]

I updated the attached file by making a new column in the data sample table [CREATED_DATE_date_only].  Then modified the links and adjusted the USERELATIONSHIP formula to point to the new column.  When CREATED_DATE is just a date with no time you can see it works.

[jdbuchanan71 · Accepted solution]

Thank you for sharing.  The problem is, your Created_Date has a time stamp on it so there are no matching rows in the 'Date'[Date] column.  

When you get it from the direct query source do a CONVERT(Date, Created_Date) on it and it should work.  That will drop the time portion.

[Anonymous]

@jdbuchanan71  - Thanks for your prompt reply. Would you mind sharing the details in the sample workbook? You can email me directly if you want on gajjarsapna0@gmail.com or post it to drop box. The reason I am asking is because it is showing below message. Or do you want me to create another column with above formula?

[jdbuchanan71]

sure, here is my version of your file.  I just set the formatting on the columns to show the full date/time to see the time stamps.

You would replace the CREATED_DATE formula in your direct query.  Assuming it is coming from SQL it would be

• SELECT CONVERT ( DATE, CREATED_DATE ) AS CREATED_DATE FROM SourceTable

instead of just 

• SELECT CREATED_DATE FROM SourceTable

[Anonymous]

Oh okies. You’re asking me to change the format of the create_date in the backend. So there isn't any workaround option in PowerBI?

[Anonymous]

@davehus , @jdbuchanan71 

Attached sample workbook. I am using directquery but these are the few columns present in my original table with same format. 

You can see  I have active relationship with close date and inactive with create date. 

I want to use month from date table and find out revenue by close date as it is active relationship. I want to find out revenue by create date too with the same date field from date table. hence, I created DAX function but it isn't giving me the correct results. 

Here is the link - https://drive.google.com/file/d/1Ddm96lokiGqdX2Tj9wTAyWHslEKcSw5Y/view?usp=sharing

Can you please check?

[davehus]

Hi, Are you able to provide a sample pbix with the data model structure and some anonymised data. It may give some insight as to why your calculation is not working the way you are expecting it?

[jdbuchanan71]

@Anonymous

Can you share you .pbix (post it to dropbox and share the link here)?