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

[FJH2024]

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 🙂 

[Anonymous · Accepted solution]

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

[FJH2024]

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 🙂

Thanks for your help in advance!

[FJH2024]

Hi Albert,

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