Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors