Forum Discussion
Conditional Format Based on Previous Quater Value
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.
Is there any better way to do this or am I missing something?
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.
2 Replies
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.