Conditional formatting based on previous 'filtered column' in Matrix.

[mf5333]

There are a couple of questions and answers regarding the question to apply conditional formatting based on the previous year/month column in a matrix visual.

but i have a different requirement.

My user needs to filter by a slicer for years to view in the matrix columns, for example he selects 2020-2022-2024 in the slicer, so the columns in the matrix have only this 3 years.

and then he wan't to compare the values and apply conditional formatting based if the value of 2022 is going up or down vs 2020 and the same by the other columns.(this is not static, and can even be that there will be a 2 year difference).

So  I cannot just compare month over month in a measure, and can't figure out how to make it work.

So if anyone can help me how to archive my needs with calculations in a measure or any other idea. It will  be really appreciated.

[v-kongfanf-msft]

Hi @mf5333 ,

Try creating two metrics, one to find the difference between 2020 and 2022 and the other to use as a conditional judgment. Refer to a formula similar to the following:

Difference =
VAR SelectedYears =
    VALUES ( 'Sales'[Year] )
VAR Year2020 =
    CALCULATE ( SUM ( 'Sales'[Value] ), 'Sales'[Year] = 2020 )
VAR Year2022 =
    CALCULATE ( SUM ( 'Sales'[Value] ), 'Sales'[Year] = 2022 )
RETURN
    IF (
        2022
            IN SelectedYears
                && 2020 IN SelectedYears,
        Year2022 - Year2020,
        BLANK ()
    )

Color = 
IF([Difference] > 0, "Green", IF([Difference] < 0, "Red", "Yellow"))

Best Regards,
Adamk Kong

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

[AllisonKennedy]

@mf5333 This will be tricky = you need to identify exactly what you want the formatting rules to be. 

It sounds like you want to compare to the previous year within the filter context? So you'll need to use DAX to do this and need to make sure to KEEPFILTERS on the year, but then clear the visual level filters. Using ALLSELECTED might help with this... it's an interesting question,

Here's a start for a measure that will help you get the previous year, but skip one or two or however many years are skipped in the slicers/filters: 

_ fx Format Amount Dynamic Date Compare = 

VAR _CurrentYear = SELECTEDVALUE( 'Date'[Year] ) 
VAR _PreviousYear = MAXX( FILTER( ALLSELECTED( 'Date'[Year] ) , 'Date'[Year] < _CurrentYear ), 'Date'[Year] ) -- Keep slicers and report filters on Year, but get the Year before the one in the visual filter context
RETURN
_PreviousYear

You need to use this in a visual that has Year in rows or columns (based on your description I think it will be in columns).

From here, you can use that _PreviousYear measure to recalculate the amount in the context of the year. @ mention me if you need further help with that part.

[mf5333]

 hi thanks for the reply. i try to implement it but can't get it work

Can you help me figure out what my mistake is?

Here is a screenshot of my screen.

[lbendlin]

Please read about Visual Calculations. They are ideally suited for your scenario.