Connecting two dates columns

[Alex_0201]

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. 

[Icey]

Hi @Alex_0201 ,

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

[amitchandak]

@Alex_0201 , 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-Y...


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/

[Alex_0201]

Hi @amitchandak Many thanks!

Sorry, but I am an absolute beginner here, could you please specify in details what needs to be done here.

[Alex_0201]

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.

[amitchandak · Accepted solution]

@Alex_0201 , yes. Then you can join with date table.

[DebbieE]

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