cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Taff4Ever Helper I
Helper I

Conditional Formatting based on Previous Columns

Hi All

 

Conditional formatting is easy enough to understand, but I am struggling to apply it in the way I want, so need some help.

 

Here is my basic table.  It shows data use over a couple of months, and I want to simply highlight if that particular user has increased or decreased their use each month.

 

The data comes from 3 seperate data sources whcih will be added to up to a max 6 month period.

 

But how do i ask conditional formatting to look at the previous column, then format accordingly?!

 

2020-01-23_11-10-10.png
Any help most welcome.....thanks

2 REPLIES 2
Highlighted
sduffy Helper I
Helper I

Re: Conditional Formatting based on Previous Columns

Hi @Taff4Ever 

 

You should be able to use something similar to this:

 

I created a new Calcultated Column called 'Nov_Higher_Lower' in this column I return a value of -1, 0 or 1 depending on the sign of the number when I subtract Novembers value from Octobers value. 

 

Nov_Higher_Lower = SWITCH(SIGN('Table1 (2)'[Nov 2019(kb)]-'Table1 (2)'[Oct 2019 (kb)]), 0, 0, -1, -1, 1, 1)
 
I then run a Rules based conditional formatting on 'Nov 2019(kb)' based on the results of 'Nov_Higher_Lower'HigherLower.PNG
 
You would also do the same with another new colum for Dec_Higher_Lower .....
 
I'm sure there might be a cleaner way to do this, but I hope this helps.
 
Sduffy
Taff4Ever Helper I
Helper I

Re: Conditional Formatting based on Previous Columns

Thanks for the suggestion @sduffy  - appreciate the help, but seems there is an issue reading the October data - which is on a seperate data source?

 

Used the following:

Nov_Higher\Lower = SWITCH(SIGN('Nov2019'[Volume (kb)]-'Oct2019'[Volume (kb)]),0,0,-1,-1,1,1)

 

But the result is:

A single value for column 'Volume (kb)' in table 'Oct2019' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors