Forum Discussion
donovan_smith44
6 years agoFrequent Visitor
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 passe...
- 6 years ago
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:
- 6 years ago
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" )
jdbuchanan71
6 years agoSuper User
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: