Forum Discussion

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

Hi MFelix 

I have a date table and a shipment_date attribute which contains duplicate dates that have a relationship. Using:

Condittional formatting =
SWITCH (
TRUE (),
COUNT ( Query1[COUNT] )
<= CALCULATE (
COUNT ( Query1[COUNT] ),
SAMEPERIODLASTYEAR ( DateTable[Date].[Date])
), "#ff8800")
 
In advanced options of conditional formatting based on field value i get the following result:
So somewhere the calculation is wrong only august and september 2019 should be orange
 
MFelix's avatar
MFelix
Icon for Super User rankSuper User
7 years ago

Hi Anonymous ,

 

Believe that your issue is regarding the .[DAte] in your formula try the following.

 

Condittional formatting =
SWITCH (
TRUE (),
COUNT ( Query1[COUNT] )
<= CALCULATE (
COUNT ( Query1[COUNT] ),
SAMEPERIODLASTYEAR ( DateTable[Date])
), "#ff8800")

Regards,

MFelix

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi MFelix  

     

    Nah I tried that doesn't make a difference for the result. I'm not sure what makes the higher values appear orange, it seems pretty random but it might be something in the dataset. I will have a look at it, but any suggestions?

    EDIT: forgot to mention shipment_date is date/time so for example 24/07/2018 00:00:00 can this cause issues creating a relationship with a date table? That would seem the only obvious explanation.  checked changed to date no changes

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

      Hi Anonymous ,

       

      Sorry for the question but your calendar table is starting on January 1st and ending on December 31st (can be different years)correct?

       

      Regards,

      MFelix

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        MFelix 

        Yes it starts at 01-01-2018 ends at 31-12-2019