Conditional Formatting Measure Highlighting Dates in Past and Future

[Gandarthvader]

Hello,

I currently have a measure created to highlight the date in a table if it's within the next 60 days YELLOW, but it also included today.

What I'd like to do, and can't figure out, is how to make it so that if the date in the column is today's date or before today's date, change the highlight to GREEN, and keep the dates within the next 60 days to stay YELLOW. Anything past 60 days in the future will have no highlight.

Below is my current measure:

Open Color =

var _datediff=DATEDIFF(MAX('Current'[Current OTP]),TODAY(),DAY)

return

IF(

    MAX('Current'[Current OTP])<= TODAY() + 60,"#FFFF99"

    )

Thank you for any help!!

[wardy912]

Hi @Gandarthvader 

 Give this a try

Date Highlight =
VAR TargetDate = SELECTEDVALUE('YourTable'[YourDateColumn])
VAR TodayDate  = TODAY()

RETURN
SWITCH(
    TRUE(),
    TargetDate <= TodayDate, "#00FF00",        -- GREEN
    TargetDate > TodayDate && TargetDate <= TodayDate + 60, "#FFFF00",  -- YELLOW
    BLANK()
)

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

[cengizhanarslan · Accepted solution]

Please try the measure below:

Open Color =
VAR _Date    = MAX ( 'Current'[Current OTP] )
VAR _Today   = TODAY ()
RETURN
    SWITCH (
        TRUE (),
        _Date <= _Today,              "#92D050",  
        _Date <= _Today + 60,         "#FFFF99",
        BLANK ()
    )

[Gandarthvader]

Worked like a charm. Thank you!!

[lbendlin]

Open Color =
var _datediff=DATEDIFF(MAX('Current'[Current OTP]),TODAY(),DAY)
return SWITCH(TRUE(),
_datediff>60,BLANK(),
_datediff>0,"yellow",
_datediff>-60,"green"
 )