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,2024...
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.

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.

    • Sandeep13's avatar
      Sandeep13
      Helper III
      1 year ago

      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

      1824922118001728345963

       

       

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