Calculate Difference in Time Between Rows for Each Time an Incident is Modified

[chart]

Hello,

The only relevant columns for this are modifiedon and TicketCalc. I'm trying to calculate the time in hours, minutes, seconds between each time a certain ticket is modified. Pretty much for every instance in TicketCalc with the same value, I want to see the time between that instance and the previous instance based on the modifiedon date and time.

So for TicketCalc 112662, I want the hours, minutes, seconds between 12/1/2021 4:06:44PM and 12/7/2021 10:38:25PM. 

Can anyone help with this? I've tried so many DAX columns and I can't get it right.

Thank you!

Chart

[TomMartens · Accepted solution]

Hey @chart ,

You have to be aware that the Power BI data model does not know the data type duration. For this reason I would start with creating a column with Power Query that contains modifiedon as a decimal number, convert this to data type duration. Then you can transform this into seconds using the transformation Duration --> Total Seconds:

 Then you can calculate the difference between the values, and reformat it using a measure similar to the one below:

Duration = 

var _TotalSeconds = CALCULATE( SUM( 'Labor Report'[Total Seconds] ) )
return
if( NOT( ISBLANK( _TotalSeconds ) )

    ,var _Days = TRUNC(DIVIDE(_TotalSeconds , 3600 * 24 ) )
    var RemainingSecondsFromDay = MOD( _TotalSeconds , 3600 * 24 )
    var _Hours = TRUNC(DIVIDE( RemainingSecondsFromDay , 3600 ) )
    var RemaingSecondsFromHour = MOD( RemainingSecondsFromDay , 3600 )
    var _Minutes = TRUNC(DIVIDE( RemaingSecondsFromHour , 60 ) )
    var RemainingSecodndsFromHour = MOD( RemaingSecondsFromHour , 60 )
    return
    IF( _Days = 0
        , _Hours & "h " & _Minutes & "min " & RemainingSecodndsFromHour & "s"
        , _Days & " days " & _Hours & "h " & _Minutes & "min " & RemainingSecodndsFromHour & "s"
    )
    ,BLANK()
)

Hopefully, this provides some ideas on how to tackle your challenge.

If not, please provide a pbix file using Power BI Desktop that contains sample data but still reflects your data model (tables, calculated columns, relationships between tables, measures). Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

Regards,

Tom

[chart]

That worked! Thank you so much, @TomMartens !