Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Matrix conditional formatting if value is lower then previous year then red

Hey guys, I have a matrix which displays  count of shipments per month for 2018 and 2019 so for example the row for june will contain 2 numbers that of 2018 and 2019.   Now if the count of shipmen...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Managed to fix it by instead of count using sum in the formale since my table already"counts" the values. 

    Condittional formatting =
    SWITCH (
    TRUE (),
    SUM('Export'[COUNT] )
    < CALCULATE (
    SUM('Export'[COUNT] ),
    SAMEPERIODLASTYEAR ( DateTable[Date])
    ), "#ff8800")
    final result
    Extra credit to MFelix  for all the help provided!
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Managed to fix it by instead of count using sum in the formale since my table already"counts" the values. 

Condittional formatting =
SWITCH (
TRUE (),
SUM('Export'[COUNT] )
< CALCULATE (
SUM('Export'[COUNT] ),
SAMEPERIODLASTYEAR ( DateTable[Date])
), "#ff8800")
final result
Extra credit to MFelix  for all the help provided!
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi, I wanted to do the same except I want it to highlight if its greater than the previous date (I have weekly data). I just flipped the < sign however, its highlighting the first column as well for all rows for some reason, I guess assuming that the date before it is 0. Can you please help me MFelix 

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    Hi Anonymous ,

     

    This calculation is based on the year basis so because it uses sameperiodlastyear so the needs is not only to change the signal.

     

    Don't know how you have your model setup but you should use something similar to:

    CALCULATE (
SUM('Export'[COUNT] ),
FILTER (ALL(DateTable), DateTable[Date] >= Min(DateTable[Date]) - 7 && DateTable[Date] <= Min(DateTable[Date]) - 1
)

     

    I'm assuming you are using dates to the week detail on your visualization.

     

    If not can you please share the sample of your setup.

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for your reply MFelix ! Yes yes I changed the code to suit the weekly period as well so here is a summary of what I'm trying to do

       

      So, I am trying to remove the formatting from the first column. This is essentially weekly quantity of a different products, with products on x axis and weeks on y axis. The idea is to highlight the cell if the value is more (green) or less (pink) than the previous week. I created this measure and applied as field value in conditional formatting.


      format =
      SWITCH (
      TRUE (),
      SUM(Append1[PDQ] )< CALCULATE (SUM(Append1[PDQ] ),DATEADD(Append1[MinDate],-7,DAY)), "pink",
      SUM(Append1[PDQ] )> CALCULATE (SUM(Append1[PDQ] ),DATEADD(Append1[MinDate],-7,DAY)),"lightgreen")

       

       

       

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        5 years ago

        Hi Anonymous ,

         

        Can you share a sample of your data model?