Conditional Formatting on Matrix based on same measure comparison

[mikeminu]

Good afternoon everyone,

I hope everyone is well and can maybe help me with a problem I am trying to solve.

I created a matrix visual with dates on the columns and index names on the rows. I wanted to show the evolution of the index's prices over the years so I created a measure to calculate the price based on the year. If it is a past year then it should be the average of real price, if current year the average of real + future price and if future year then just the average of the expected future prices. So far, so good and I ended with a table like this: (simplified for sharing purposes)

	2021	2022	2023	2024	2025
Index A	0.4	1.1	0.7	0.6	0.9
Index B	2.1	4.7	5.1	4.9	3.2
Index C	1.3	1.4	1.5	0.9	1.3

Then I thought it would be nice to add some icon conditional formatting as a visual aid to indicate if the price for each index increased or decreased compared to the previous year. This is where things started to get more complicated. Since all the values come from the same measure I couldnt easily compare them. I tried creating a new measure to compare each value with the one on the previous column and return a value from -1 to 1 to then apply the conditional formatting based on this measure, but I cannot seem to find a way to effectively call and compare the columns.

Here is the measure I tried to create:

Would really appreciate some help on this matter!

Many thanks in advance,

Miguel 

[Anonymous · Accepted solution]

Thanks for the reply from lbendlin, please allow me to provide another insight.
Hi @mikeminu ,

Please refers to the following steps.

Below is my testing data table.

Create a date table and establish a relationship with the source table.

DimDate = ADDCOLUMNS(
    CALENDAR(MIN('Table'[Date]),MAX('Table'[Date])),
    "Year", YEAR([Date])
)

Use the following measure to set the conditional format of the matrix values.

Measure = 
VAR SelectedYear = SELECTEDVALUE(DimDate[Year])

VAR CurrentYearValue = CALCULATE([Sum of price], DimDate[Year]=SelectedYear)
VAR PreviousYearValue = CALCULATE([Sum of price], DimDate[Year]= SelectedYear-1)
RETURN
SWITCH(
    TRUE(),
    CurrentYearValue>PreviousYearValue,1,
    CurrentYearValue<PreviousYearValue,-1,
    0
)

The final result is as follows. Hopefully it will meet your needs.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

[lbendlin]

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.