Forum Discussion
Format Values by Month
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.
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:
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" )
3 Replies
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:
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" )Thank you so much this is great.
