Forum Discussion
Conditional formatting based on previous 'filtered column' in Matrix.
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.
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.