conditional format in matrix based on selected years

[Tizzerbelle]

I have created an order intake report where the user can select the years they want to compare.

There is a matrix which shows all the months and the selected years with the value

I want to add conditional formatting which shows red if less than the previous year selected and green if greater than.

I can obviousy do this based on the previous year but cannot workout how to do this based on the selected years.

Below is an image of how i want it to work - done in excel where you can add based on the row.  Any ideas how to achieve this?

[Anonymous]

Hi @Tizzerbelle ,

I made simple samples and you can check the results below:

Measure = var _t = CALCULATE(MAX('Table'[Value]),SAMEPERIODLASTYEAR('Table'[Date].[Date]))
RETURN IF(_t>MAX('Table'[Value]),"RED","GREEN")

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Tizzerbelle]

Hi

Thank you for your assistance, I have tried to use this but as i am using a measure within the table - i cannot use MAX so i tried this:

ConditionalColPY = var _t = CALCULATE(MAXX(All('DateTable'),[Orders Intake]),SAMEPERIODLASTYEAR(DateTable[PKDate]))
RETURN IF(_t>MAXX(All(DateTable),[Orders Intake]),"RED","GREEN")

As you can see i am using  a related Date table.

The conditional formatting just shows green for all rows, which i suspect is due to the MAXX statement.

Any suggestions?

[Tizzerbelle]

I have also tried this:

ConditionalColPY = 
VAR CurrentPeriodValue = [Orders Intake]
VAR LastYearValue = CALCULATE(
    [Orders Intake],
    SAMEPERIODLASTYEAR(ALL(DateTable[PKDate]))
)
RETURN 
IF(
    LastYearValue > CurrentPeriodValue,
    "RED",
    "GREEN"
)

But this shows all rows and columns as red

[Anonymous]

Hi @Tizzerbelle ,

Can you share the sample pbix file you used?

Best regards,
Community Support Team_ Scott Chang

[Tizzerbelle]

@Anonymous  Sure -  here is a pbix with dummy data in

Conditional format report 

[Anonymous]

Hi @Tizzerbelle ,

I've been working on this for quite a while and I realized that I misunderstood your question and that you are not necessarily comparing to last year. There doesn't seem to be a way to do this for this kind of variable comparison. Despite slicer filtering the data, the DAX expression is still conditioned on the previous year of the calculation.

Best regards,
Community Support Team_ Scott Chang

[Tizzerbelle]

@Anonymous Ok, Thanks for trying, hopefully Microsoft will add this feature in the future

[Anonymous · Accepted solution]

Hi @Tizzerbelle ,

I thought about it differently and got the answer you wanted:

Measure = 
var _current=[Orders Intake]
var _rank=[Measure 2]
var _table=ADDCOLUMNS(ADDCOLUMNS(SUMMARIZE(ALLSELECTED('DateTable'),[FinYear],[Financial_Fiscal_Month_Name]),"order",[Orders Intake]),"rank",[Measure 2])
var _pre=MAXX(FILTER(_table, [rank] =_rank-1 && [Financial_Fiscal_Month_Name] in VALUES(DateTable[Financial_Fiscal_Month_Name])),[order])
RETURN _pre

Measure 2 = RANKX(ALLSELECTED('DateTable'),[FinYear],MAX('DateTable'[FinYear]),ASC,Dense)

Measure 3 = SWITCH(TRUE(),
ISBLANK([Measure]),"black",
[Orders Intake]<=[Measure],"red","green")

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Tizzerbelle]

Absolutely brilliant - works a treat