topic Date and Time diff from two tables in DAX Commands and Tips

Date and Time diff from two tables

SOS4 — Thu, 01 Sep 2022 14:47:20 GMT

Hi,

I want to track how long it takes users to respond to work 'tickets'.
I have two dates from two related tables. Work details open date - work requests open date
But I can't figure out how to do the calculation and show the results in hours and minutes.

This is a sample of my data. I'd really appreciate any assistance!
Table 1: Work Details

LCLEnterDate

2022-05-04T21:41:04.9400000

2022-05-04T21:57:18.3830000

2022-05-05T13:36:06.1930000

2021-06-08T15:26:25.5530000

Table 2: WorkRequests

DateEntered

2021-08-25T07:21:38.5170000

Re: Date and Time diff from two tables

amitchandak — Thu, 01 Sep 2022 15:12:42 GMT

SOS4 , The tables need to have some common id table like work order id then you can have

sumx(Values(WorkOrder[Work Order ID]) , calculate(Datediff(Min(Table1[Date]), Max(Table2[Date]), Day) ) )

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solution-8e3eccb41bda

Re: Date and Time diff from two tables

SOS4 — Thu, 01 Sep 2022 16:20:30 GMT

Actually I was mistaken, The tables do not have unique values in common, though they are both connected to a third table.
Could this work?

Re: Date and Time diff from two tables

Anonymous — Wed, 07 Sep 2022 05:24:37 GMT

Hi SOS4 ,

Here are the steps you can follow：

1. Create calculated column.

Duration HH:MM:SS = var _maxdate=MAXX(ALL('WorkRequests'),'WorkRequests'[DateEntered]) var _all= SUMX(FILTER(ALL('Work Details'),'Work Details'[LCLEnterDate]=EARLIER('Work Details'[LCLEnterDate])), 'Work Details'[LCLEnterDate] - _maxdate) * 1 var _days=INT(_all) var _time=_all-_days return FORMAT( _days, "#0D-" ) & FORMAT( _time, "hh:mm:ss")

2. Result:

If you need pbix, please click here.

Date and Time diff from two tables.pbix

Best Regards,

Liu Yang

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