Adding highlight to a column in visual based on date

[Anonymous]

I would like the column in this matrix visual where the reporting end of week is the most recent one passed to be highlighted in yellow.  For example, today is 2/24 so 2/19 would be the most recent previous end of week date.  I looked under conditional formatting but I don't see how to make this work with the options.  Any help would be appreciated.

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "EndofWeek",
    var d =[Date]
    return
    MAXX(
        FILTER(
            CALENDARAUTO(),
            YEAR([Date])*100+WEEKNUM([Date])=YEAR(d)*100+WEEKNUM(d)
        ),
        [Date]
    )-1
)

Relationship:

You may create a measure and set conditional format based on the measure as below.

Color Control = 
var d = MAX('Calendar'[EndofWeek])
var lastweeknum = 
CALCULATE(
    MAX('Calendar'[EndofWeek]),
    FILTER(
        ALL('Calendar'),
        [EndofWeek]<
        CALCULATE(
            MAX('Calendar'[EndofWeek]),
            FILTER(
                ALL('Calendar'),
                [Date]=TODAY()
            )
        )
    )
)
return 
IF(
    d = lastweeknum,
    "red"
)

Result(Today is 3/1/2021):

Best Regards
Allan

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

[Anonymous]

One thing i noticed about that calendar:  one of the days is after the after the end-of-week, every week.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

You may modify the 'Calendar' table as below.

Calendar = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "EndofWeek",
    var d =[Date]
    return
    MAXX(
        FILTER(
            CALENDARAUTO(),
            YEAR([Date])*100+WEEKNUM([Date])=YEAR(d)*100+WEEKNUM(d)
        ),
        [Date]
    )
)

Result:

 

Best Regards

Allan

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

[VijayP]

@Anonymous 

You can Create a measure using in The Field Value option in Conditional Formatting

if(Selectedvalue(Date)=26 Feb 2021 - 7 , "Red", "")
(for this =26 Feb 2021 - 7

you need to build dynamic field based on the behaviour of your data).

[Anonymous]

I tried this but it won't let me select reportingEndofWeek as a field - maybe because it's a date.