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
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
Memorable Member
Memorable Member

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
Memorable Member
Memorable Member

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

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.