Format Values by Month

[donovan_smith44]

Hi all, I have a table below:

I wanted to know if it's possible in DAX to make the formatting dynamic with the current month. I would like this table to format values green if month has passed, blue if current month, gray if upcoming month and ideally update automatically when a new year starts.

I was thinking something like using the stock data, using an IF() statement and then applying today(), month(), and switch() (for the switching of text colors) might be a way to do it, but im not sure how.

is this possible? Thanks.

[jdbuchanan71 · Accepted solution]

Hello @donovan_smith44 

You can write a measure to read the month and return the color you want then use that measure to apply formatting to the text.

First the measure:

Formatting = 
VAR _Month = MONTH ( LASTDATE ( Dates[Date] ) )
VAR _ThisMonth = MONTH ( TODAY() )
RETURN 
SWITCH ( 
    TRUE(),
    _Month < _ThisMonth, "#62B153",
    _Month = _ThisMonth, "#A2D2E3",
    "#A2A2A2"
)

Those are the hex codes for the colors in your screen shot.

Then we apply the formatting:

[parry2k · Accepted solution]

@donovan_smith44 you are on right track, just create a measure with colors and then use that in conditional formatting. You can change column name and color as per your requirement.

KPI = 
VAR __currentMonthStart = EOMONTH ( TODAY(), -1 ) + 1
VAR __currentMonthEnd = EOMONTH  ( TODAY(), 0 )
VAR __month = MAX ( Phase[Month] )
RETURN 
SWITCH ( TRUE(),
    __month < __currentMonthStart, "Red",
    __month >= __currentMonthStart && __month <= __currentMonthEnd, "Blue",
    "Green"
)

[donovan_smith44]

Thank you so much this is great.

[jdbuchanan71 · Accepted solution]

Hello @donovan_smith44 

You can write a measure to read the month and return the color you want then use that measure to apply formatting to the text.

First the measure:

Formatting = 
VAR _Month = MONTH ( LASTDATE ( Dates[Date] ) )
VAR _ThisMonth = MONTH ( TODAY() )
RETURN 
SWITCH ( 
    TRUE(),
    _Month < _ThisMonth, "#62B153",
    _Month = _ThisMonth, "#A2D2E3",
    "#A2A2A2"
)

Those are the hex codes for the colors in your screen shot.

Then we apply the formatting: