Date table and table in power bi

[Anonymous]

Hello all,

I have a question for you about Power

I have a date table and a table with name and start date.   There is no unique id to link these two tables.    I would like customer to choose the dates from date table (Begin date and End date) , and calculate the service time.  (Date - start date).

Name	Date of Hire	Year of Hire
Joe	01/01/2000	2000
Mike	07/01/2000	2000

Date	Year	Begin Date column	End Date column
01/01/2023	2023	01/01/2023	01/02/2023
01/01/2023	2023	01/01/2023	01/02/2023
.....	...	...	...
06/30/2023	2023	06/30/2023	06/30/2023

If customer picks 01/01/2023 as begin date, 06/30/2023 as end date.  

Joe's date of service is 3 years, Mike's is 2 years etc.

How can I build the logic?

Thank you for your help!

[Anonymous]

[Anonymous]

Hi @Anonymous ,

According to your statement, I think Joe and Mike are both hired on 2000/1/1. 

So I am confused about how to get Joe's date of service is 3 years, Mike's is 2 years etc when you pick 01/01/2023 as begin date, 06/30/2023 as end date.   I think the service time should be 06/30/2023 - 01/01/2023.

If you want to calculate the difference between days, I suggest you to try DATEDIFF.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you @Anonymous ,

Thank you so much for your reply. My question was whether Power BI can pick any dates from date table and calculate the years of service based on Date of Hire.

These two tables are not connected with any unique ID.

Sincerely,

Hiroki