March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello Guys,
I want to calculate the hold time between 2 calls of indiviual source on specific date i mean
suppoose source 602 call date is 1-9-2021 11:16:06 and his end time is 1-09-2021 11:16:37 and source 602 2nd call start at 1-9-2021 11:17:10 i have to calculate the hold time between 1-09-2021 11:16:37 and 1-9-2021 11:17:10 for source 602 and same for 603 please help how to do this
Thanks
pra137
Solved! Go to Solution.
Hi @pra137
You can try this code to achieve your goal.
Diff =
VAR _CurCallStart = 'Table'[Call Start]
VAR _LastCallEnd =
CALCULATE (
MAX ( 'Table'[Call End] ),
FILTER (
'Table',
'Table'[Src] = EARLIER ( 'Table'[Src] )
&& 'Table'[Date] = EARLIER ( 'Table'[Date] )
&& 'Table'[Call End] < EARLIER ( 'Table'[Call End] )
)
)
VAR _TIMEDIFF = _CurCallStart - _LastCallEnd
RETURN
IF ( _LastCallEnd = BLANK (), TIME ( 0, 0, 0 ), _TIMEDIFF )
Result is as below. Note to change the [Diff] column Data type to Time and Format to h:nn:ss.
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.
@pra137
For A all Custom Values are duration between first call to last call
Try this solution and let me know if it helps!
Proud to be a Super User!
Thank sir but I have to calculate break time between 2 calls I mean 1st call end time is 11:16:37 should be subtract from start time of 2nd call eg 11:16:37-12:16:00
@pra137 any chance you can provide sample data and desired output clearly?
I want result like diff column in below pic
Hi @pra137
You can try this code to achieve your goal.
Diff =
VAR _CurCallStart = 'Table'[Call Start]
VAR _LastCallEnd =
CALCULATE (
MAX ( 'Table'[Call End] ),
FILTER (
'Table',
'Table'[Src] = EARLIER ( 'Table'[Src] )
&& 'Table'[Date] = EARLIER ( 'Table'[Date] )
&& 'Table'[Call End] < EARLIER ( 'Table'[Call End] )
)
)
VAR _TIMEDIFF = _CurCallStart - _LastCallEnd
RETURN
IF ( _LastCallEnd = BLANK (), TIME ( 0, 0, 0 ), _TIMEDIFF )
Result is as below. Note to change the [Diff] column Data type to Time and Format to h:nn:ss.
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.
to get the result as below use this file
Proud to be a Super User!
Thanks but it shows time between call I want the hold time between 2 calls
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
169 | |
144 | |
90 | |
70 | |
58 |