Forum Discussion
Date and Time Difference
Hi Matic20 ,
For your requirement, you could create the measure below.
difference = VAR pauseticket = CALCULATE ( MAX ( 'Table'[LOG CREATION DATE] ), FILTER ( 'Table', 'Table'[EVENT NAME] = "pauseticket" ) ) VAR resumeticket = CALCULATE ( MAX ( 'Table'[LOG CREATION DATE] ), FILTER ( 'Table', 'Table'[EVENT NAME] = "resumeticket" ) ) VAR diff = DATEDIFF ( pauseticket, resumeticket, SECOND ) VAR hours = INT ( diff / 3600 ) VAR minutes = INT ( MOD ( diff - ( hours * 3600 ), 3600 ) / 60 ) RETURN hours & "hr" & minutes & "mints"Here is the output.
Hope this can help you.
Best Regards,
Cherry
v-piga-msft wrote:Hi Matic20 ,
Normally, we could use DATEIFF function to calculate the datetime difference.
I still have a little confused about your scenario.
If it is convenient, could you share your desired output so that we could help further on it.
Best Regards,
Cherry
Difference btn the eventname pauseticket and resumeticket (7/26/2019 18:49) and (7/26/2019 14:44)
Result = 4hr and 5 mints
LOG CREATION DATE EVENT NAME TICKET ID 7/26/2019 19:10 postinternalnote 16639 7/26/2019 19:10 closeticket 16639 7/26/2019 19:10 postreply 16639 7/26/2019 18:49 resumeticket 16639 7/26/2019 18:26 postreply 16639 7/26/2019 14:44 pauseticket 16639 7/26/2019 14:44 postreply 16639 7/26/2019 14:44 claimticket 16639 7/26/2019 14:34 postinternalnote 16639 7/26/2019 14:34 overdueticket 16639 7/26/2019 14:34 postinternalnote 16639 7/26/2019 14:34 postinternalnote 16639 7/26/2019 14:33 postreply 16639 7/26/2019 14:33 createticket 16639
Hi Matic20 ,
For your requirement, you could create the measure below.
difference = VAR pauseticket = CALCULATE ( MAX ( 'Table'[LOG CREATION DATE] ), FILTER ( 'Table', 'Table'[EVENT NAME] = "pauseticket" ) ) VAR resumeticket = CALCULATE ( MAX ( 'Table'[LOG CREATION DATE] ), FILTER ( 'Table', 'Table'[EVENT NAME] = "resumeticket" ) ) VAR diff = DATEDIFF ( pauseticket, resumeticket, SECOND ) VAR hours = INT ( diff / 3600 ) VAR minutes = INT ( MOD ( diff - ( hours * 3600 ), 3600 ) / 60 ) RETURN hours & "hr" & minutes & "mints"Here is the output.
Hope this can help you.
Best Regards,
Cherry
v-piga-msft wrote:Hi Matic20 ,
For your requirement, you could create the measure below.
difference = VAR pauseticket = CALCULATE ( MAX ( 'Table'[LOG CREATION DATE] ), FILTER ( 'Table', 'Table'[EVENT NAME] = "pauseticket" ) ) VAR resumeticket = CALCULATE ( MAX ( 'Table'[LOG CREATION DATE] ), FILTER ( 'Table', 'Table'[EVENT NAME] = "resumeticket" ) ) VAR diff = DATEDIFF ( pauseticket, resumeticket, SECOND ) VAR hours = INT ( diff / 3600 ) VAR minutes = INT ( MOD ( diff - ( hours * 3600 ), 3600 ) / 60 ) RETURN hours & "hr" & minutes & "mints"Here is the output.
Hope this can help you.
Best Regards,
Cherry
Thank you so much, this really did help
Hi,
As v-piga-msft mentioned, you can use DateDIFF in DAx (Not in Powerquery).
Or in PowerQuery you can simply create a custom column with :[resumeticket] - [pauseticket]
If you want this in a different format, have a look here:
https://docs.microsoft.com/nl-nl/powerquery-m/duration-functions
If you want more help, please let me know what exact datatype you want to have and in what format.
Robbe
