Date/time calculation across tables

[Anonymous]

I have two tables related by column 'ID': Table A many to 1 Table B. Table A has a column called Deadline that is in Date format (no time). Table B has a column called Authorized Date that has a date and a time in text format. I would like to create a new column in Table B that gives me the difference called Date Diff.

Table A

Table B

My preference would be to assume Deadline is 5pm and add the time to the date, but that isn't a necessity. I'd like the difference to be in minutes. I know I need to use DATEDIFF, but I'm struggling with how to get the columns in the right format and have no idea how to add 5pm to Deadline. I've tried the Parse function in Power Query on the Authorized Date field, but it just gives me the date and loses the time. I'm happy to have any number of intermediate columns necessary through Power Query or to do it with Dax.

One last confounding factor, some of the rows are blank for both of these fields.

Thanks!

[d_gosbell]

So the following calculated column expression should do this for you

Date Diff = DATEDIFF(RELATED('Table A'[Deadline]) + 17/24, 'Table B'[Authorized Date],MINUTE)

Note the following:

• To reference the [Deadline] column in Table A you need to use the RELATED function
• In Tabular models there is no Date Only data type, so technically the Deadline column will come in as midnight, and behind the scenes dates are internally stored as the number of days since the  30 Dec 1899. So adding 1 to a date will return the day after or adding 0.5 will add 12 hours. So adding 17/24 moves the time forward to 17:00 hours (5pm)
• Once the above 2 details are taken into account you can simply use DATEDIFF to get the number of minutes between the two values

[Anonymous]

Thanks for the great explanation! What about the fact that Authorized Date is a text field and not Date/Time?

[d_gosbell]

---

@Anonymous wrote:

Thanks for the great explanation! What about the fact that Authorized Date is a text field and not Date/Time?

---

It looks like a format that Power BI should be able to parse. If you want to do a date diff you'd need to change the data type of this column.