cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gavin254
Frequent Visitor

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

 

Pic.png

 

Any help greatly appreciated

2 ACCEPTED SOLUTIONS

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 )

View solution in original post

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 

 

View solution in original post

5 REPLIES 5
SolomonovAnton
Responsive Resident
Responsive Resident

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 )

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

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 

 

I am glade to hear it 🙂

 

please mark topic as resolved )

and click cudoes fror me )

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.