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

## Comparing times against each other and showing a negative difference (if there is one)

Hi guys,

Hoping someone might be able to help me with this one!

I have some different times I'm comparing against each other - "Time Logged", "Time Estimated" and "Time Remaining". When the Time Logged is greater than the Time Estimated, I want the Time Remaining to show a negative figure. It currently is only showing as 00:00:00:

The example highlighted shows the Time Logged as 06:30:00 and Time Estimated as 05:00:00, so ideally I'd like the Time Remaining to show -01:30:00 as it's run an hour and a half past the estimate.

Does anyone know if I can alter the format of the "Time Remaining" column so it will show a negative time figure if the estimate has overrun? Or is there some sort of calculation I need to run?

I'm pretty new to using PowerBI so still getting to grips with it, so I'm sorry if this is a really obvious question! Thanks for your help in advance 🙂

1 ACCEPTED SOLUTION
Community Support

Hi @FJH2024 ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

Create a measure

``````Time Remaining =
VAR _t = SELECTEDVALUE('Table'[Time Logged]) - SELECTEDVALUE('Table'[Time Estimated])
RETURN
IF(
SELECTEDVALUE('Table'[Time Logged])>SELECTEDVALUE('Table'[Time Estimated]),
FORMAT(_t,"-hh:mm:ss"),
FORMAT(_t,"hh:mm:ss")
)``````

Final output

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

3 REPLIES 3
Community Support

Hi @FJH2024 ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

Create a measure

``````Time Remaining =
VAR _t = SELECTEDVALUE('Table'[Time Logged]) - SELECTEDVALUE('Table'[Time Estimated])
RETURN
IF(
SELECTEDVALUE('Table'[Time Logged])>SELECTEDVALUE('Table'[Time Estimated]),
FORMAT(_t,"-hh:mm:ss"),
FORMAT(_t,"hh:mm:ss")
)``````

Final output

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Frequent Visitor

Hi Albert,

Just another small question if that's okay. This formula worked perfectly, however do you know if there's a way to alter the formulation slightly so the new Time Remaining column shows a total at the bottom? I've tried adding the SUM() function (as Google suggested) however it's throwing an error, so wondered if you had any thoughts 🙂

Frequent Visitor

Hi Albert,

This worked perfectly! Thank you so much for your help, I really appreciate it 🙂