Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Conditional Formatting based on Time for multiple conditions

Hello Everyone,

 

I have a table that has columns Name, Time (formated as HH:MM: SS AM/PM) and a KPILight using Unichar(11044). My goal is to have the KPI Light change color based on the following:

 

If Time is +- 15 min from 3:00 PM turn KPI Light Green OR if Time is +- 15 min from 4:00 AM turn KPI Light Green, else turn KPI Light Red.

 

I've been playing around with different IF formulas in the formula bar but haven't been able to get this working. Any guidance would be greatly appreciated.

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

    You may try to add a time column in query editor first.For example:

    Then create a rule measure like below and use it for KPILigtht column with conditional formatting.

    Rule =
IF (
    ABS ( DATEDIFF ( MAX ( 'KPI'[Time.1] ), TIME ( 15, 0, 0 ), MINUTE ) ) <= 15
        || ABS ( DATEDIFF ( MAX ( 'KPI'[Time.1] ), TIME ( 16, 0, 0 ), MINUTE ) ) <= 15,
    1,
    2
)

    Regards,

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous 

    You may try to add a time column in query editor first.For example:

    Then create a rule measure like below and use it for KPILigtht column with conditional formatting.

    Rule =
IF (
    ABS ( DATEDIFF ( MAX ( 'KPI'[Time.1] ), TIME ( 15, 0, 0 ), MINUTE ) ) <= 15
        || ABS ( DATEDIFF ( MAX ( 'KPI'[Time.1] ), TIME ( 16, 0, 0 ), MINUTE ) ) <= 15,
    1,
    2
)

    Regards,