cancel
Showing results for
Did you mean:
Member

## Conditional formatting matrix: Comparing cells to adjacent value

Hello everyone -

I'd like to conditionally format the cells of a matrix to indicate whether or not sales are trending up or down, compared to the previous time period. I say time period, because I would like this to update as I "drill-down" from Year > Quarter > Month.

Additionally, I don't want a simple binary response - ie. "are sales greater or smaller than the previous time period". Ideally, I'd like a colour gradient that reflects the magnitude of the difference between time periods.

I feel as thought this is feasible using the conditional formatting optoins, but can't seem to get my head around how to construct the Field (assuming a Measure) I need.

Will add some images of my data and some examples of how I'd like the colours (not a gradient), having issues right now.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Conditional formatting matrix: Comparing cells to adjacent value

hi, @Sweet-T

I continue to work on this requirement, and today I find a way may achieve your requirement.

Now, this is a new way for you refer to;

basic data

Step1:

Add a Year Quarter Number column

`Year Quarter Number = YEAR ( Table1[Date] ) * 100 + INT ( FORMAT ( [Date], "q") ) `

Step2:

```%change =
VAR previousDate =
CALCULATE (
MAX ( Table1[Date] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Qty] > 0
&& Table1[Date] < MAX ( Table1[Date] )
)
)
VAR previousYQ =
CALCULATE (
MAX ( Table1[Year Quarter Number] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Qty] > 0
&& Table1[Year Quarter Number] < MAX ( Table1[Year Quarter Number] )
)
)
VAR previousREG =
CALCULATE (
SUM ( Table1[Qty] ),
FILTER ( ALLSELECTED ( Table1 ), Table1[Date] = previousDate )
)
VAR previousYQTOTAL =
CALCULATE (
SUM ( Table1[Qty] ),
FILTER ( ALLSELECTED ( Table1 ), Table1[Year Quarter Number] = previousYQ )
)
VAR previousQty =
IF (
ISFILTERED ( Table1[Date].[Quarter] ),
previousYQTOTAL,
IF ( ISFILTERED ( Table1[Date].[Month] ), previousREG )
)
RETURN
DIVIDE ( SUM ( Table1[Qty] ) - previousQty, previousQty, 0 )```

Step3:

Result:

here is pbix, please try it.

https://www.dropbox.com/s/j3sraf440cbjs13/Conditional%20formatting%20for%20matrix.pbix?dl=0

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Member

## Re: Conditional formatting matrix: Comparing cells to adjacent value

I'd first like the formatting to occur quarterly

And to update accordingly when I drill down to monthly

Community Support Team

## Re: Conditional formatting matrix: Comparing cells to adjacent value

hi, @Sweet-T

For your first requirement, it can be achieved by adding a measure like: PREVIOUSMONTH Function/PREVIOUSQUARTER Function/PREVIOUSYEAR Function then set Conditional formatting like it.

for example:

```Qty QoQ% =
IF (
ISBLANK ( CALCULATE ( SUM ( Table1[Qty] ), PREVIOUSQUARTER ( 'Date'[Date] ) ) ),
BLANK (),
CALCULATE ( SUM ( Table1[Qty] ) )
- CALCULATE ( SUM ( Table1[Qty] ), PREVIOUSQUARTER ( 'Date'[Date] ) )
)```

It works well.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

## Re: Conditional formatting matrix: Comparing cells to adjacent value

hi, @Sweet-T

I continue to work on this requirement, and today I find a way may achieve your requirement.

Now, this is a new way for you refer to;

basic data

Step1:

Add a Year Quarter Number column

`Year Quarter Number = YEAR ( Table1[Date] ) * 100 + INT ( FORMAT ( [Date], "q") ) `

Step2:

```%change =
VAR previousDate =
CALCULATE (
MAX ( Table1[Date] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Qty] > 0
&& Table1[Date] < MAX ( Table1[Date] )
)
)
VAR previousYQ =
CALCULATE (
MAX ( Table1[Year Quarter Number] ),
FILTER (
ALLSELECTED ( Table1 ),
Table1[Qty] > 0
&& Table1[Year Quarter Number] < MAX ( Table1[Year Quarter Number] )
)
)
VAR previousREG =
CALCULATE (
SUM ( Table1[Qty] ),
FILTER ( ALLSELECTED ( Table1 ), Table1[Date] = previousDate )
)
VAR previousYQTOTAL =
CALCULATE (
SUM ( Table1[Qty] ),
FILTER ( ALLSELECTED ( Table1 ), Table1[Year Quarter Number] = previousYQ )
)
VAR previousQty =
IF (
ISFILTERED ( Table1[Date].[Quarter] ),
previousYQTOTAL,
IF ( ISFILTERED ( Table1[Date].[Month] ), previousREG )
)
RETURN
DIVIDE ( SUM ( Table1[Qty] ) - previousQty, previousQty, 0 )```

Step3:

Result:

here is pbix, please try it.

https://www.dropbox.com/s/j3sraf440cbjs13/Conditional%20formatting%20for%20matrix.pbix?dl=0

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: Conditional formatting matrix: Comparing cells to adjacent value

Lin, THANK YOU!

This is really cool, I apprecaite you taking the time to figure this out. This is a great base; I simplified my model for the sake of explaining my need. I will take what you have given me and try it this weekend, then report back.

Thanks again,

T