cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors