Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Conditional Formatting for Dates

Hi, 

 

I have a table with a column of dates that I want to format based on when the date is. If the date is more than 2 weeks away I want it to be green, if it is is within 2 weeks of today I want it to be yellow, and if it is today or past today I want it to be red. It is a calculated column based on the date someone enrolled, and the entire table is a calculated table For instance the column that says 45 days - if the date is 12/15 I want it to be green, if the date is 11/9 I want it to be yellow, and if the date is 5/30 I want it to be red. Can someone help me do this? 

 

Need Help
Show and Tell
Tutorial Requests
  • PhilipTreacy's avatar
    PhilipTreacy
    3 years ago

    Hi Anonymous 

     

    Download PBIX file with the example below 

     

    Create a measure like this to determine the color for each date

     

    CF = 

VAR _today = TODAY()

RETURN

SWITCH( TRUE(),

    SELECTEDVALUE('DataTable'[45 Days]) > _today + 14, "#0F0", 
    
    SELECTEDVALUE('DataTable'[45 Days]) > _today, "#FF0", 

    "#F00"
)

     

     

     

    Then add a Conditional Formatting rule to the 45 days column and apply the CF rule just created

     

     

     

    Here are the settings for the conditional formatting

     

    Giving this

     

    regards

     

    Phil

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    3 years ago

    Hi Anonymous 

     

    Download PBIX file with the example below 

     

    Create a measure like this to determine the color for each date

     

    CF = 

VAR _today = TODAY()

RETURN

SWITCH( TRUE(),

    SELECTEDVALUE('DataTable'[45 Days]) > _today + 14, "#0F0", 
    
    SELECTEDVALUE('DataTable'[45 Days]) > _today, "#FF0", 

    "#F00"
)

     

     

     

    Then add a Conditional Formatting rule to the 45 days column and apply the CF rule just created

     

     

     

    Here are the settings for the conditional formatting

     

    Giving this

     

    regards

     

    Phil

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      This is perfect!! Thank you!!

  • MahyarTF's avatar
    MahyarTF
    Memorable Member
    3 years ago

    Hi,

    I usually create another column and fixed the color code in that, then use it in my visual for Conditional Formatting :

    DateDiffColor = if (DATEDIFF(Sheet248[Date1], Sheet248[Date2], DAY) >= 14, "#6bec58",
                        if (DATEDIFF(Sheet248[Date1], Sheet248[Date2], DAY) < 15 &&
                                DATEDIFF(Sheet248[Date1], Sheet248[Date2], DAY) > 1,
                            "#Cfe008",
                            "#E60324"
                           )
                       )

     

    Appreciate your Kudos and please mark it as a solution if it helps you