Conditionally format matrix cell value based on previous year value

[Anonymous]

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

[SolomonovAnton · Accepted solution]

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 )

[SolomonovAnton]

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 formating

if you problem is resiolved please mark topic as resolved )

and click cudoes fror me )

[Anonymous]

Many thanks for the reply

But there appears to be a slight problem with the measure. It's giving me a snytax error to do with ')' but i can't see where the issue would be as everything appears to be closed off correctly

[SolomonovAnton · Accepted solution]

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 )

[Anonymous · Accepted solution]

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 

[SolomonovAnton]

I am glade to hear it 🙂

please mark topic as resolved )

and click cudoes fror me )