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
FJH2024
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:

 

FJH2024_1-1707837660426.png

 

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
v-heq-msft
Community Support
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:

vheqmsft_0-1707877674459.png

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

vheqmsft_1-1707877716634.png

 

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

View solution in original post

3 REPLIES 3
v-heq-msft
Community Support
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:

vheqmsft_0-1707877674459.png

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

vheqmsft_1-1707877716634.png

 

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

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 🙂

 

FJH2024_0-1707992246156.png

 

Thanks for your help in advance!

Hi Albert,

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.