Negative Duration shown wron in hh mm ss

[Applicable88]

Hello, 

I'm using this workaround for getting duration in seconds into this form hh:mm:ss.

Its a quite know workaround provided by Chelsie Eiden:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/td-p/793639

How ever I have a calculation of a median which ends up negative as -44 seconds. 
First Measure to get difference between two timestamps as seconds.

Measure1 = Datediff (time1-time2), seconds)

Second Measure to check if the Median of the timestamp:

Duration = MEDIAN('Table'[Measure1])

as show it ends up really like -44 seconds:

If i drag that measure with another dimension I really get -44 seconds for that category displayed.

However if I'm using the hh mm ss conversion workaround it shows -00:40:84 instead of  -00:00:44

I hope someone dealt with negative duration before. All positive values are shown correctly.

Thank you very much in advance. 

Best. 

[parry2k · Accepted solution]

@Applicable88 maybe tweak it a bit

Measure 2 = 
VAR MedianValue = -44 //this will be your median value measure

VAR Negative = IF ( MedianValue < 0, -1, 1 )  
VAR Duration = ABS ( MedianValue )

// There are 3,600 seconds in an hour
VAR Hours = INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
VAR Result = 
// We put the hours, minutes and seconds into the proper "place"
Hours * 10000 + Minutes * 100 + Seconds
RETURN  Result * Negative

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[mahoneypat]

Please see this article on a good way to calculate and format durations. It is not necessary to separately calculate hours, min, and sec.

Calculate and Format Durations in DAX – Hoosier BI

Pat

[Applicable88]

@mahoneypat , thank you.

I will defintely take a look. Your point "You don't need a Duration Column" sounds very interesting, since I'm unfortunately working a lot with Durations in PowerBi. 

[parry2k · Accepted solution]

@Applicable88 maybe tweak it a bit

Measure 2 = 
VAR MedianValue = -44 //this will be your median value measure

VAR Negative = IF ( MedianValue < 0, -1, 1 )  
VAR Duration = ABS ( MedianValue )

// There are 3,600 seconds in an hour
VAR Hours = INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
VAR Result = 
// We put the hours, minutes and seconds into the proper "place"
Hours * 10000 + Minutes * 100 + Seconds
RETURN  Result * Negative

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Applicable88]

wow @parry2k , great tweak. Thank you so much. 

[parry2k]

@Applicable88 not sure how you are converting seconds to hh:mm:ss but maybe this is what you need:

seconds in time format = TIME ( 0, 0, ABS ( [YourDifferenceMeasure] ) )

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Applicable88]

Hello @parry2k,

I'm using this workaround to get hh:mm:ss:

VAR Duration = calculate (Median(DurationMeasure))
// There are 3,600 seconds in an hour
VAR Hours = INT ( Duration / 3600)
// There are 60 seconds in a minute
VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 
VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number
RETURN
// We put the hours, minutes and seconds into the proper "place"
Hours * 10000 + Minutes * 100 + Seconds

 As I said, it works well with positve values, but not if the duration is negative.

I tried your formula, but its not a value I can drag into a bar chart, rather a different display of the seconds:

Whenever I drag it into a bar charts "values" it will jumpt to Quickinfo immediately since its more like a dimension then a measure I guess. Hope there are other ways to work around that problem. 

Cheers. 

[rsbin · Accepted solution]

@Applicable88,

In this Measure, there are calculations in there which I presume are premised on a +ve Duration.

I think if you add in an ABS[DurationMeasure], and then in the RETURN put in a conditional IF:

IF [YourDurationMeasure] is -ve, then multiply by -1.

So, in other words, format it as if it was positive since those values work, and then transform it back to your negative value at the end.

Hope this might work for you.

[Applicable88]

@rsbin thank you 🙂 . This is basically the solution that parry2k also implemented. 

[rsbin]

@Applicable88, @parry2k ,

I'm thinking (hoping? ) Parry used my answer to create his solution. 😀

Gotta give it to him.....he is much, much better at writing syntax on the fly than I am.

Glad you got your answer.