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...
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

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