Conditional Formatting based on Previous Columns

[Anonymous]

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?!

Any help most welcome.....thanks

[Anonymous]

Hi @Anonymous 

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'

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

[Anonymous]

Thanks for the suggestion @Anonymous  - 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.