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.

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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