Forum Discussion

Tizzerbelle's avatar
Tizzerbelle
Frequent Visitor
2 years ago
Solved

conditional format in matrix based on selected years

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?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Tizzerbelle
      Frequent Visitor
      2 years ago

      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's avatar
        Tizzerbelle
        Frequent Visitor
        2 years ago

        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