color formating in matrix color based on previous column

[Arnoh]

Hello,

I have created a matrix with financial date based on the company's fiscal year and periods.

The periods are not equal to months.

I would like to give the background collor based on the value on the previous column in the matrix.

In the picture below i would like the value 3880 for 2019 to be red because it's more then the value for 2018.

In the picture below i zoomed in on column now showing the period.

Now i would like the value 288 for 2019 period 2 to be green because it's less then the value for 2019 periode 1.

Any suggestions would be great.

Thanx

[jdbuchanan71]

Hello @Arnoh 

You can use a measure to compare the amount vs the amount in the prior period and apply the formatting based on the measure rather than the cell value.  We use ISINSCOPE to check if we are at the month or year level.

Formatting Measure = 
VAR SalesLastMonth = CALCULATE( [Sales Amount], DATEADD( 'Date'[Date], -1, MONTH ) )
VAR SalesLastYear = CALCULATE( [Sales Amount], DATEADD( 'Date'[Date], -1, YEAR ) )
RETURN
[Sales Amount] - 
IF ( ISINSCOPE ( 'Date'[Calendar Year Month] ), SalesLastMonth, SalesLastYear)

[Arnoh]

Hi Thanx for your input,

I am trying to use your solution but it's not giving the result i would like. 

I was already working on a solution but it gives a performance issue. Perhaps you can help me any further.

Thanx

vorige periode improved = 
var huidigeperiode = SELECTEDVALUE(zmutatie[boekingsperiode])
var huidigboekjaar = SELECTEDVALUE(zmutatie[boekjaar])
var maxperiode = CALCULATE(max(zmutatie[boekingsperiode]);all(zmutatie))
return

sumx(
filter(all(zmutatie);
if(huidigeperiode=BLANK();zmutatie[boekjaar]=huidigboekjaar-1&& zmutatie[grootboekcode]=SELECTEDVALUE(zmutatie[grootboekcode])&& zmutatie[administratiecode]=SELECTEDVALUE(zmutatie[administratiecode]);
if(huidigeperiode=1;
zmutatie[boekingsperiode]=maxperiode && zmutatie[boekjaar]=huidigboekjaar-1&& zmutatie[grootboekcode]=SELECTEDVALUE(zmutatie[grootboekcode]) && zmutatie[administratiecode]=SELECTEDVALUE(zmutatie[administratiecode]);
zmutatie[boekingsperiode]=huidigeperiode-1 && zmutatie[boekjaar]=huidigboekjaar&& zmutatie[grootboekcode]=SELECTEDVALUE(zmutatie[grootboekcode])&& zmutatie[administratiecode]=SELECTEDVALUE(zmutatie[administratiecode]))))
;[bedrag_sum])