Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
108 | |
106 | |
93 | |
69 |
User | Count |
---|---|
167 | |
132 | |
130 | |
96 | |
91 |