Multiple Colors for Conditional Formatting a Date Field

[PowerBINoob24]

I currently have the following measure that I use to apply condition formating to a date field:

Approved Date Format = if( max('Execution Status'[Approved Date]) >max('Execution Status'[Due Date]),"red")

This works great; however, I need to modify it so that if the approved date is blank and the Due Date is greater than today's date, I need that field to be orange.  I've tried a few things, but I just get lost.

Thanks in advance for your help.

[v-yueyunzh-msft · Accepted solution]

Hi , @PowerBINoob24 

I think the dax is right.

Switch ( True() ,

isblank(max('Execution Status'[Approved Date])) && max('Execution Status'[Due Date]) > today() , "orange" ,

max('Execution Status'[Approved Date]) >max('Execution Status'[Due Date]),"red"

)

Please confirm that your conditions are:
(1) [Approved Date]=BLANK and [Due Date]Show "orange" > TODAY()
(2) [Approved Date]> [Due Date] when showing "red"

For the conditions you give, it is not explicitly stated that [Approved Date] = BLANK() and [Due Date]<=TODAY() and so on.

For your question, you can give us some detailed example data and corresponding output sample data in tabular form so that we can better help you.

Best Regards,

Aniya Zhang

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

[amitchandak]

@PowerBINoob24 , Try like

Switch ( True() ,

isblank(max('Execution Status'[Approved Date])) && max('Execution Status'[Due Date]) > today() , "orange" ,

max('Execution Status'[Approved Date]) >max('Execution Status'[Due Date]),"red"

)

[PowerBINoob24 · Accepted solution]

Not sure why switching the lines made a difference, but now it works.

Switch ( True() ,

max('Execution Status'[Approved Date]) >max('Execution Status'[Due Date]),"red",

isblank(max('Execution Status'[Approved Date])) && max('Execution Status'[Due Date]) < today() , "orange"

)

[PowerBINoob24]

Unfortunately that didn't work.  The cells with the approved date populated will format red, but the cells that are blank and past due do not turn orange.

[v-yueyunzh-msft · Accepted solution]

Hi , @PowerBINoob24 

I think the dax is right.

Switch ( True() ,

isblank(max('Execution Status'[Approved Date])) && max('Execution Status'[Due Date]) > today() , "orange" ,

max('Execution Status'[Approved Date]) >max('Execution Status'[Due Date]),"red"

)

Please confirm that your conditions are:
(1) [Approved Date]=BLANK and [Due Date]Show "orange" > TODAY()
(2) [Approved Date]> [Due Date] when showing "red"

For the conditions you give, it is not explicitly stated that [Approved Date] = BLANK() and [Due Date]<=TODAY() and so on.

For your question, you can give us some detailed example data and corresponding output sample data in tabular form so that we can better help you.

Best Regards,

Aniya Zhang

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