Forum Discussion

Sandeep13's avatar
Sandeep13
Helper III
1 year ago
Solved

comparison between 3 months in table matrix

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_Name202410202411202412202501
Axis Bank237081582429093023702073350
Bajaj Finance Limited493595748892218527261323528652
Bank Of Baroda120958051208909011917036 
Canara Bank2271056922718906218116631534421
CapFloat Financial Services Pvt Ltd100110901

 

"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 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

5 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Sandeep13
      Helper III
      1 year ago

      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. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.