Conditional Format Based on Previous Quater Value

[Anonymous]

I am trying to conditional format based on previous Qtr value. If the cell value is greater than previous qtr value then color the current cell as "RED". Data sample is below.

Name	Date	Year	Qtr	Hours
John	01-Jan-20	2020	1	20
John	01-May-20	2020	2	30
John	01-Aug-20	2020	3	10
John	01-Dec-20	2020	4	40
Rick	01-Jan-20	2020	1	10
Rick	01-May-20	2020	2	20
Rick	01-Aug-20	2020	3	10
Rick	01-Dec-20	2020	4	10
John	01-Jan-21	2021	1	40
John	01-May-21	2021	2	10
John	01-Aug-21	2021	3	20
John	01-Dec-21	2021	4	10
Rick	01-Jan-21	2021	1	60
Rick	01-May-21	2021	2	30
Rick	01-Aug-21	2021	3	10
Rick	01-Dec-21	2021	4	10

I used a measure as below to condtional format and the output is correct but it is always coloring the first column as RED since there is no data before that period. I am using a Calendar Table (DateDim) too.

ConFormat1 = SWITCH(TRUE(),SUM(Sheet1[Hours]) > CALCULATE (SUM ( Sheet1[Hours] ),

PREVIOUSQUARTER( DateDim[Date])), "RED")

If you look at below 2020 Q2 color is correct since previous qtr value is less. But 2020 Q1 shouldn't be RED. 

Is there any better way to do this or am I missing something?

[Anonymous]

Worked perfectly.. Thanks a lot..

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please try the below measure for the font color conditional formatting -> format by Field value.

The table names are a bit different than yours, but I hope you can easily fix it to apply to your model.

ConFormat Font Color =
SWITCH (
TRUE (),
SUM ( Data[Hours] )
> CALCULATE ( SUM ( Data[Hours] ), PREVIOUSQUARTER ( 'Calendar'[Date] ) )
&& NOT ISBLANK (
CALCULATE ( SUM ( Data[Hours] ), PREVIOUSQUARTER ( 'Calendar'[Date] ) )
), "Red"
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/