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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    Absolutely brilliant - works a treat