Forum Discussion
Inactive relationship Date vs Create_Date
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.
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
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 - Thanks for your prompt reply. Would you mind sharing the details in the sample workbook? You can email me directly if you want on [email protected] 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?
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
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?