Forum Discussion

Sandeep13's avatar
Sandeep13
Icon for Helper III rankHelper 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.

danextian's avatar
danextian
Icon for Super User rankSuper 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.