Forum Discussion

Gandarthvader's avatar
Gandarthvader
Regular Visitor
5 months ago
Solved

Conditional Formatting Measure Highlighting Dates in Past and Future

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!!
  • cengizhanarslan's avatar
    cengizhanarslan
    5 months ago

    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 ()
    )

4 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    5 months ago

    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 ()
    )
  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 months ago
    Open Color =
var _datediff=DATEDIFF(MAX('Current'[Current OTP]),TODAY(),DAY)
return SWITCH(TRUE(),
_datediff>60,BLANK(),
_datediff>0,"yellow",
_datediff>-60,"green"
 )
  • wardy912's avatar
    wardy912
    Icon for Super User rankSuper User
    5 months ago

    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!