Conditional Formatting Based on Current and Fixed Time

[markefrody]

Hi,

I'm wondering if this is possible. I have a data that contains break times for each person. 

Driver Name	Break Time
Person A	9/21/2020 09:40:26 AM
Person B	9/21/2020 10:27:37 AM
Person B	9/21/2020 10:00:44 AM

What I want is to create a table wherein the color the cell changes based on that person's break time vs current time. Below is the
color code to be followed:
1. Yellow: 45 to  60 mins before break time
2. Red: 44 or less minutes before break time
3. Gray: If on/after the break time

Here is an example on how it should look:

So the cell color changes based on the current/real time.

Please let me know if this can be achieved in Power BI. 

Best regards,
Mark V.

[v-jingzhang · Accepted solution]

@markefrody 

Here are the steps you may take (I name the source table as ‘BreakTime’😞

1. Create a measure:

Time Diff = DATEDIFF(NOW(),MAX(BreakTime[Break Time]),MINUTE)

2. Use conditional formatting for Break Time: Format by Rules, Based on field Time Diff, and create the rules as you like.

Otherwise, you can also use format by field for background color. Create a measure:

Color Measure =
VAR __currentTime = NOW()
VAR __timeDiff = DATEDIFF(__currentTime,MAX(BreakTime[Break Time]),MINUTE)
RETURN
IF(__timeDiff < 60 && __timeDiff >= 45,"Yellow",IF(__timeDiff < 45 && __timeDiff > 0,"Red",IF(__timeDiff <= 0,"Gray","White")))

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.

[markefrody]

Thank you for your response and assistance @amitchandak and @v-jingzhang. They are very helpful.

I used @v-jingzhang method which I suits my needs. 

Many thanks to you both.

[amitchandak]

@markefrody , I did not get your logic completely. But You can have measure like

//Change logic as per need

Measure =
var _time = now() -[Break Time] // or datediff([Break Time] ,now(),min)
return
Switch ( true(),
_time >0 ,"Grey",
_time <-60 , "Red",
_time <-45 , "Yellow",
"White"
)

And use Conditional formatting with "Field Value" option

refer : https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values