Forum Discussion

PowerBINoob24's avatar
PowerBINoob24
Resolver I
3 years ago
Solved

Multiple Colors for Conditional Formatting a Date Field

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.
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

     

  • PowerBINoob24's avatar
    PowerBINoob24
    3 years ago

    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"

    )

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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's avatar
      PowerBINoob24
      Resolver I
      3 years ago

      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's avatar
      PowerBINoob24
      Resolver I
      3 years ago

      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's avatar
        v-yueyunzh-msft
        Community Support
        3 years ago

        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