cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Arnoh Regular Visitor
Regular Visitor

color formating in matrix color based on previous column

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.

nivo jaar.png

 

 

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.

 

nivo periode.png

 

 

Any suggestions would be great.

 

Thanx

2 REPLIES 2
Super User
Super User

Re: color formating in matrix color based on previous column

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 Regular Visitor
Regular Visitor

Re: color formating in matrix color based on previous column

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

nivo periode v2.png

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])
 

 

 

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 484 members 4,440 guests
Please welcome our newest community members: