formatting time differences

[hastingse14]

hi,

I’ve got data like this with check in and check out times.  There's a column of the time difference which is decimal because it’s the fraction of 24h

I have a column which shows if the Time Difference is > 7 hours created by this

GT 7h = if(sum(Time_Log[Time Diff]) > 0.29166 , 1, 0)

I want to add another test to see if a time difference is more than 10 hours.  Then I want to add formatting to the Time Difference column based on if it’s > 7h or > 10h

thank you.

G

[v-ssriganesh]

Hello @hastingse14,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[krishnakanth240]

Hi @hastingse14 

7 hours = 7/24 = 0.29
10 hours = 10/24 = 0.42

Calculated columns:

TimeDiff_Hours = Time_Log[Time Diff] * 24

/*To show the value in hours (rather than fraction of 24)*/

GT_7h_Column = IF( Time_Log[Time Diff] > (7/24), 1, 0 )

GT_10h_Column = IF( Time_Log[Time Diff] > (10/24), 1, 0 )

Measures (for aggregated checks in visuals or cards)
GT 7h (Measure) = IF( SUM(Time_Log[Time Diff]) > (7/24), 1, 0 )

GT 10h (Measure) = IF( SUM(Time_Log[Time Diff]) > (10/24), 1, 0 )

Conditional formatting:

/*Create a measure that returns a hex color depending on the row’s hours*/

TimeDiff_Color =
VAR hours = SELECTEDVALUE(Time_Log[Time Diff]) * 24
RETURN
SWITCH(
TRUE(),
hours > 10, "#D32F2F", // red for > 10h
hours > 7, "#FBC02D", // amber for > 7h
"#FFFFFF" // white (default)
)

@hastingse14

[v-ssriganesh]

Hello @hastingse14, 

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @PhilipTreacy for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[PhilipTreacy · Accepted solution]

Hi @hastingse14 

Download an example PBIX file here

I’d rewrite the measure so that it works out if a Time Diff is >7h or >10h and then decides what colour to use for the cell background, rather than return decimal values like 1 which you then have to write more code to decide colours

GT 7h = 

VAR _time_diff = SELECTEDVALUE('Time_Log'[Time Diff])
VAR _7h = 420 / 1440
VAR _10h = 600 / 1440

RETURN 

SWITCH( TRUE(),

    _time_diff > _10h, "red",

    _time_diff > _7h, "orange",

    "green"

)

The SWITCH function requires a default value which I have set to "green" here but if you don’t want a default (all cells will get this unless they are red or orange) then make it "" instead.

Click on the down arrow beside the Time Diff column in the visual and then select Conditional Formatting-> Background Color

Choose these settings and click OK

Your table should like something like this

Regards

Phil