comparison between 3 months in table matrix

[Sandeep13]

Hi All

I want to highight below table matrix. So everytime I will have 4 months in my matrix like below. Latest month(202501) will be as running month. so now I want to compare 202410,202411,202412 with each other for every member and if any of the month is drop by 10 %  or more it should be highlight In RED colour.

Just for referene: Axis Bank memeber will compare values of 202410,202411,202412 and highlight any of the month if its drop by 10 % or more.

This is sample data for refernce.

Member_Name	202410	202411	202412	202501
Axis Bank	23708158	24290930	23702073	350
Bajaj Finance Limited	49359574	8892218	52726132	3528652
Bank Of Baroda	12095805	12089090	11917036
Canara Bank	22710569	22718906	21811663	1534421
CapFloat Financial Services Pvt Ltd	100	110	90	1

"CapFloat Financial Services Pvt Ltd"  has value for 100 ,110 ,90 So 90 will be highligh in RED.because in 202412 has drop of almost around 10%

Thanks 

@MI @alexp @Bibi @ADAV @Expert 

[v-denglli-msft]

Thanks for the reply from danextian, please allow me to provide another insight.
Hi @Sandeep13 ,

Create a measure to be used as a matrix value. Displays 0 if the value is null.

Sum of Value = IF ( SUM ( 'Table'[Value] ) = BLANK (), 0, SUM ( 'Table'[Value] ) )

Sets the conditional format for this measure: if the value is 0 the font color is red.

Use the following measure to set the matrix value background color.

Flag = 
VAR previousMonthValue =
    CALCULATE ( [Sum of Value], PARALLELPERIOD ( 'Date'[Date], -1, MONTH ) )
VAR currentMonthValue = [Sum of Value]
RETURN
    IF (
        DIVIDE ( previousMonthValue - currentMonthValue, previousMonthValue ) > 0.1,
        "Red"
    )

The final result is as follows. Hopefully it will meet your needs.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sandeep13]

Thanks for your insight. Please dont consider running month(latest month) 202501 as this is running and values will be always less.

I want to compare only last 3 months for example 202410,202411,202412 only.

If any of the month drop is by 10 % or more it should be highlight in RED color. 

[v-denglli-msft · Accepted solution]

Hi @Sandeep13 ,

Please try the following measure.

Flag = 
VAR previousMonthValue =
    CALCULATE ( [Sum of Value], PARALLELPERIOD ( 'Date'[Date], -1, MONTH ) )
VAR currentMonthValue = [Sum of Value]
VAR monthlyDifference =
    DATEDIFF ( MAX ( 'Date'[Date] ), TODAY (), MONTH ) -- Calculate the month difference to now.
RETURN
    IF (
        monthlyDifference > 0 && monthlyDifference <= 3, -- Filtering values within the last three months
        IF (
            DIVIDE ( previousMonthValue - currentMonthValue, previousMonthValue ) > 0.1,
            "Red"
        )
    )

Please see the attahced pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Sandeep13]

Thanks It worked, Just one thing wanted to ask In my data when I use your logic I can see both the months highlights 202410 and 202412 for bank of india limited. I thik this happend bcoz of 11800172 is comparing with 202410 and 202412 both

1824922

11800172

8345963

I wanted to highlight only one month but anyway this will also work

[danextian]

Hi @Sandeep13 

If your data is in that format, it needs to be properly shaped as tabular data first. Otherwise, ensure you have a separate date dimension table related to your fact table in a one-to-many relationship.

Create these measures

Sum of Value = 
SUM ( Data[Value] )

Value Previous Month = 
CALCULATE (
    [Sum of Value],
    PREVIOUSMONTH ( DatesTable[Date] ),
    REMOVEFILTERS ( DatesTable )
)
MoM % =
VAR _DIFF = [Sum of Value] - [Value Previous Month]
RETURN
    DIVIDE ( _DIFF, [Value Previous Month] )

Conditional Formatting Fill Color = 
IF ( [MoM %] <= -0.10, "#ff746c" )
//or you can use just red, other hexadecimal, RGB or RGBA colors

Apply conditional formatting fill color to the Sum of Value measure using the Conditonal Formatting Fill Color  measure using the  field value option

Please see the attached pbix for the details.