cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Martine_
Regular Visitor

Condital Formatting on Duration in hours depending on times between

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:

Martine__0-1635249904874.png

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. 

Martine__1-1635250009295.png

 


Any suggetions on how I can achieve this?


Thanks very much in advance.

1 ACCEPTED SOLUTION
vojtechsima
Super User
Super User

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

vojtechsima_0-1635359656899.png

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.

View solution in original post

3 REPLIES 3
vojtechsima
Super User
Super User

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

vojtechsima_0-1635359656899.png

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.

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
Super User
Super User

@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

 



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors