Forum Discussion

donovan_smith44's avatar
donovan_smith44
Frequent Visitor
6 years ago
Solved

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...
  • jdbuchanan71's avatar
    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:

  • parry2k's avatar
    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"
    )