Conditional Formatting Based on Month-Over-Month Matrix Values

[weezle80]

I am completely lost trying to figure out how to conditionally format my matrix columns so it calls out values that are less than the previous month. From my AgencyAssignment table, I have the Contract Name field under Rows, Month (Date Hierarchy) under Columns, and Sum of Agencies under Values. Contract A had 2 Agencies for Jan, Feb, Mar, & April, for a total of 8. However, Contract B dropped from 65 Agencies in January to 63 Agencies in February, so that 63 needs to be highlighted. Same for the drops for Contract D, and so on. I have been digging through forums and YouTube and have tried multiple things and nothing is working out right. Can you help?

[Anonymous · Accepted solution]

Hi @weezle80 ,

I have created a simple sample, please reer to my pbix file to see if it helps you.

Create 2 measures.

Measure =
VAR _1 =
    EDATE ( MAX ( 'Table'[DATE] ), -1 )
VAR _2 =
    CALCULATE (
        MAX ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DATE] = _1
                && 'Table'[contract name] = SELECTEDVALUE ( 'Table'[contract name] )
        )
    )
VAR _mindae =
    MINX ( ALL ( 'Table' ), 'Table'[DATE] )
RETURN
    IF ( MAX ( 'Table'[DATE] ) = _mindae, 0, MAX ( 'Table'[value] ) - _2 )

Measure 2 = IF([Measure]<0,"Red",BLANK())

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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