Forum Discussion

JMST's avatar
JMST
Frequent Visitor
2 years ago
Solved

Coloring Matrix according to Dates

Hi All!

 

I want to coloring a matrix depending on the dates, for instance, if I have dates before today I want to coloring the background in red, if I have dates between today+6 months I want to coloring the background in yellow and green in other cases.

Does anyone have any idea how to do it?

Thank you in advanced for your help!

Best regards

Need Help
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi JMST 
    You can create DAX measure for color flag :

    Flag = if (MAX('Table'[Date]) < TODAY(),"red",
           
             if (max('Table'[Date])>TODAY() && DATEDIFF(  max('Table'[Date]),TODAY(),MONTH)>=-6, "yellow",
              "green"))
    And then use it as a rule of condition

    Link to a sample file 

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

  • Uzi2019's avatar
    Uzi2019
    2 years ago

    Hi JMST 
    Please try the following solution. Hardly takes 5 mins.


    1) Create column for Next 6 month date

    Today_ = DATE(Year(TODAY()),MONTH(TODAY())+6,DAY(TODAY()))

    2) create color measure for conditional formatting
    Color Date1 =
                SWITCH(TRUE(),
                              max('Test Date'[Date Future])< TODAY(),"Red",
                              AND(MAX('Test Date'[Date Future])>=TODAY(), (min('Test Date'[Date Future])<MIN('Test                                            Date'[Today_]))),"yellow",
                             "Green"
                               )
     
        3) apply conditional formatting on cell element
           select table > cell element > Field value >select color measure

     



     

    then you will get this color formatted column.

     


    If my post helps please give kudos and accept it as a solution!
    Thanks

3 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi JMST 
    You can create DAX measure for color flag :

    Flag = if (MAX('Table'[Date]) < TODAY(),"red",
           
             if (max('Table'[Date])>TODAY() && DATEDIFF(  max('Table'[Date]),TODAY(),MONTH)>=-6, "yellow",
              "green"))
    And then use it as a rule of condition

    Link to a sample file 

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

  • Uzi2019's avatar
    Uzi2019
    Community Champion
    2 years ago

    Hi JMST 
    Please try the following solution. Hardly takes 5 mins.


    1) Create column for Next 6 month date

    Today_ = DATE(Year(TODAY()),MONTH(TODAY())+6,DAY(TODAY()))

    2) create color measure for conditional formatting
    Color Date1 =
                SWITCH(TRUE(),
                              max('Test Date'[Date Future])< TODAY(),"Red",
                              AND(MAX('Test Date'[Date Future])>=TODAY(), (min('Test Date'[Date Future])<MIN('Test                                            Date'[Today_]))),"yellow",
                             "Green"
                               )
     
        3) apply conditional formatting on cell element
           select table > cell element > Field value >select color measure

     



     

    then you will get this color formatted column.

     


    If my post helps please give kudos and accept it as a solution!
    Thanks

  • JMST's avatar
    JMST
    Frequent Visitor
    2 years ago

    Thank you for your answer! It works perfect!