cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

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

1 ACCEPTED SOLUTION
Super User

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
2 REPLIES 2
Super User

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

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Frequent Visitor

That worked! Thank you so much, @TomMartens !

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors