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 cond...
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.

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Tizzerbelle ,

       

      Can you share the sample pbix file you used?

       

      Best regards,
      Community Support Team_ Scott Chang