Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
chart
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. 

 

chart_1-1639085655163.png

 

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
TomMartens
Super User
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:

image.png

 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

View solution in original post

2 REPLIES 2
TomMartens
Super User
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:

image.png

 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

That worked! Thank you so much, @TomMartens !

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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