Forum Discussion

PraveenMRRP's avatar
PraveenMRRP
Frequent Visitor
2 years ago

Conditional Formatting on Matrix - Comparing Values per Previous Quarters Dynamically

Hi all,

 

Hoping to get some assistance - I have seen many posts that have similar issues, however, unable to get this to work myself.

I require that every quarter should compare with previous quarter if amount reduces by 10% it should change font color

For Example Product 'A'  Q1 2023 has 45 amount

                                       In next quater(Q2 2023) amount is 25 [Compared to previous quarter it is reduced by 10% so font color should change ] then

                                       In next Quarter(Q3 2023)amount is 34 [Compare to previous quarter amount I is increased so font color should not change]

 

This should be dynamic as new quarter will be added in future the logic should behave same.

 

Hoping for assistance

 

Thanks In Adavance

 

Need Help

2 Replies

  • vicky_'s avatar
    vicky_
    Icon for Super User rankSuper User
    2 years ago

    I first created a measure to calculate the change % - 

    Change (%) = 
var prevAmt = CALCULATE(SUM('Table (2)'[Amt]), PREVIOUSQUARTER('Table (2)'[Date]))
var currentAmt = SUM('Table (2)'[Amt])
var change = (currentAmt - prevAmt) / currentAmt
return IF(ISBLANK(prevAmt), BLANK(), change)

    Then go to Format Visual > Cell Elements > Font Colour > fx

    and use the following settings:
    Format style = Rules
    What field should we base this on = Change (%) Note: this is the measure I created earlier

    Rules = If value >= 0 percent and < 0.1 number then colour.