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

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

      • Tizzerbelle's avatar
        Tizzerbelle
        Frequent Visitor
        2 years ago

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