Forum Discussion
Connecting two dates columns
Hi everyone. Is it possible to connect two date columns from different tables?
I have one table called Report Journal (with several columns, amongst them are id and report date) and another table called Activity (with several columns, amongst them are id, Time and Description).
The relationship between the tables is active, and the carinality is Many to Many (can't change it).
report date and Time columns are date type.
I would like to be abel to choose a date in report date, and get the Time column filtered by the same date so I can see Description for that date.
Anonymous , yes. Then you can join with date table.
6 Replies
Anonymous , time is time or time with date. If time with date then create a date
Date =[Time].date
You can create a common date table and id table and can use them
ID Table = distinct(Union(all(Journal[ID]),all(Activity[ID])))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Time Table
https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/
Hi amitchandak Thanks a lot!
If I change the format to date/time for these columns report date and Time columns, then time is shown only as 00:00:00. I guess it means that the it is only date for both columns.
Anonymous , yes. Then you can join with date table.
Hi amitchandak Many thanks!
Sorry, but I am an absolute beginner here, could you please specify in details what needs to be done here.
Hi Alex
It would be great if you could provide a little example of the two tables and then an example of the expected result just to get a clearer picure
Hi Anonymous ,
Is this problem solved?
If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If not, please let me know.
Best Regards,
Icey
