Finding a difference between 2 columns

[MBWATSON]

Good morning,

I have a table with a column titled Status Audit Type. Two of the values in this column are Create and Done and each has a corresponding date/time in another column Titled Status Changed At.

I am needing to find the turn around time from when a message was created and when it was completed (done).

Any suggestions?

Thank you!

Melissa

[amitchandak]

@MBWATSON ,

a measure

sumx( Values(Table[message ID]), calculate(datediff(minx(filter(Table, Table[Status Audit Type] ="Create"), Table[Status Changed at]), maxx(filter(Table, Table[Status Audit Type] ="Done"), Table[Status Changed at]), hour)))

For column consider earlier

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s

[MBWATSON]

Thank you amitchandak. Should the result come out in hours then? Minutes? A numerical value for date/time?

[amitchandak]

@MBWATSON , datediff you can get in hour, minute, second , day

if you need get time, simply date diff two, but that will not sum

You can try like

time(0,0,0) + sumx( Values(Table[message ID]), calculate(datediff(minx(filter(Table, Table[Status Audit Type] ="Create"), Table[Status Changed at]), maxx(filter(Table, Table[Status Audit Type] ="Done"), Table[Status Changed at]), hour)))/24

[MBWATSON]

Throwing this back out there 🙂

The suggested formula for the measure did not work. Here is an example

This particular message took almost a year to complete. Yet here is the cycle time using the formula shown in hh:mm format

Any other suggestions @amitchandak or anyone else?

[MBWATSON]

Maybe I should clarify, I am looking for the amount of time it took to complete the work, from the create time to the done time.  When I try the second formula I get an error message.

[MBWATSON]

I found the error so that isn't an issue. The formula returns a date and time. What I need is for it to return HH:MM

[MBWATSON]

@amitchandak I was able to get this to show a time but they aren't correct. I have a Create date/time of 4/19/2022 8:20:09 and a Done date/time of 10/31/22 6:17:17 that doesn't even show in the Matrix. Some with similar dates/times showing in the Matrix as 7 hours. Any other suggestions?