Condital Formatting on Duration in hours depending on times between

[Martine_]

Hello all, 

I am working on a report that shows the duration of shifts in hours and highlights records when the duration exceeds 12 hours.
So far I splitted to date/time columns to seperate colums and I have the duration in hours and can conditional format on this column.
But now I need to add an extra condition. I would like to condital format the Duration in hours column when it exceeds 10 hours as well as the original 12, but only when there is at least 1 hour of the shift between 12:00 am and 5:00 am. At this moment I have a column Start Shift which shows the time in h:mm:ss AM (or PM) and a column End Shift which shows the time in h:mm:ss AM (or PM).

At this moment it looks like this:

So now for example I would like to show in red as well: (duration exceeds 10 hours and there is (more than) 1 hour between 12:00 am and 05:00 am. 

Any suggetions on how I can achieve this?

Thanks very much in advance.

[vojtechsima · Accepted solution]

Hi, @Martine_ 
I believe I figured out your request:

I added a couple of custom shifts to test all probable scenarios.
For your solution I added "SHIFT_ID" to identify the shift, this ID helps me then with the calculation below.
This piece of code should make your request work:

Check = 
var DurationHours = CALCULATE(
    MAX('Table'[Duration Actuals in hours]),
    FILTER('Table','Table'[SHIFT_ID]<=MAX('Table'[SHIFT_ID]))
)
var EndTime = CALCULATE(
    MAX('Table'[Actual End Time]),
    FILTER('Table','Table'[SHIFT_ID]<=MAX('Table'[SHIFT_ID]))
)
var StartTime = CALCULATE(
    MAX('Table'[Actual Start Time]),
    FILTER('Table','Table'[SHIFT_ID]<=MAX('Table'[SHIFT_ID]))
)
var EndAfterMidnight = DATEDIFF(FORMAT("12:00:00 AM","Long Time"),EndTime,MINUTE)
var StartAfterMidnight = DATEDIFF(StartTime,FORMAT("05:00:00 AM","Long Time"),MINUTE)
var StartAfterFive = DATEDIFF(FORMAT("05:00:00 AM","Long Time"),StartTime,MINUTE)
var IsAfterMidnight = IF(HOUR(EndTime)<=23 && HOUR(StartTime)<HOUR(EndTime),0,1)

var TrueCheck = SWITCH(TRUE(),
    DurationHours>=12, "Red",
    DurationHours >= 10 && DurationHours <12 && EndAfterMidnight >=60  && IsAfterMidnight=1,"Red",
    DurationHours >= 10 && DurationHours <12 && StartAfterMidnight >=60  && HOUR(StartTime) IN {0,1,2,3,4},"Red",
    DurationHours >= 10 && DurationHours <12 && StartAfterMidnight <60 && HOUR(StartTime) IN {0,1,2,3,4},"Green",
    DurationHours >= 10 && DurationHours <12 && StartAfterFive >1 && HOUR(EndTime) >5,"Green",
    DurationHours<=11.99, "Green",
    "Green"
)
return 
TrueCheck

Create a new measure, paste this code and, in your Table, select Conditional Formatting Based On Field and choose your new measure.

[Martine_]

Thank you very much for your time and effort and this solution.
I was able to fix it by using a measure that set a value for all records where the duration is >10 and start time or end time are between 1 AM and 5 AM, or shift start on or after 3 PM. Then created a column for this measure to added the conditional formatting.

But your solution looks more solid, so tried this as well and it worked, so thank again, appreciate it!

[amitchandak]

@Martine_ , You need to create a measure where you deal with duration in min and hours and then return color and use that in conditional formatting using the field value option

If this is a column use max, in case of measure you can use it as just split and get time.

Examples

Color sales = if(AVERAGE(Sales[Sales Amount])<170,"green","red")

Color = if(FIRSTNONBLANK('Table'[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170
,"lightgreen",if(FIRSTNONBLANK('Table'[Year],2014)>2018,"red","yellow"))

How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4