Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
2021-08-25T07:21:38.5170000 |
Solved! Go to Solution.
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
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
@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) ) )
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?
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |