Forum Discussion
Conditionally format matrix cell value based on previous year value
Hi
I've been searching for a solution to my request but can't seem to find one that works unfortunately so reaching out for help!
I have a simple matrix table with categories as row titles and year as column headings. The values are a simple percentage measure i've created with a particular population and the year is based on a year column within the data table
What i would like to do is simply colour the background of a cell based on the previous year value.
1. if value is greater than previous year, colour green
2. if the same, colour yellow
3. if below, colour red
Below is my example matrix table
Any help greatly appreciated
error was in the end of script
color =
var _PrevYear =CALCULATE([measure],SAMEPERIODLASTYEAR('Calendar '[Date]))
var _result =SWHITCH (TRUE(),
[measure]>_PrevYear,"green",
[measure]=_PrevYear,"yellow",
"red")if you problem is resiolved please mark topic as resolved )
and click cudoes fror me )
It wasn't actually the " that was causing the problem. The syntax needed to use 'return' instead of another variable.
color =
var _PrevYear =CALCULATE([measure],SAMEPERIODLASTYEAR('Calendar '[Date]))
Return
SWITCH (TRUE(),
[measure]>_PrevYear,"green",
[measure]=_PrevYear,"yellow",
"red")Many thanks for your help as everything is now working as i'd hoped
5 Replies
hello
you should create date table
Calendar = CARENDARAUTO()after it link the Calendar table with the FAct table you should create measure
color = var _PrevYear =CALCULATE([measure],SAMEPERIODLASTYEAR('Calendar '[Date])) var _result =SWHITCH (TRUE(), [measure]>_PrevYear,"green", [measure]=_PrevYear,"yellow", "red)there [measure] - it is the measure which calculated percent for each year
cafer it create conditonal formating based on the [color] measure
see manual about conditional formatingif you problem is resiolved please mark topic as resolved )
and click cudoes fror me )
error was in the end of script
color =
var _PrevYear =CALCULATE([measure],SAMEPERIODLASTYEAR('Calendar '[Date]))
var _result =SWHITCH (TRUE(),
[measure]>_PrevYear,"green",
[measure]=_PrevYear,"yellow",
"red")if you problem is resiolved please mark topic as resolved )
and click cudoes fror me )