How to apply conditional formatting for latest last years data in the matrix?

[sekhar438]

Hi Team,

In my Matrix is contained Year, City, and Revenue and the matrix is filter Top 2 Years.

If based on the customer selections Matrix years are changed.

Suppose: If I select customer is 219282 then the Last 2 years' data revenue (Year: 2022 is 10M and Year 2021: is 20M)

then compare the lastest last 2 years' revenue to show the difference in arrows(If the lastest top 1 year is greater than the latest top 2 then the green arrow else the red arrow).

How to create the above conditional formatting based on dynamic change Years(Lastest last year's data based on the customer selection)

Thanks,

chandrasekhar 

[v-yangliu-msft · Accepted solution]

Hi  @sekhar438 ,

Here are the steps you can follow：

1. Create calculated table.

True1 =
var _table1=
SUMMARIZE(
    'Table','Table'[customer],'Table'[Year],'Table'[Attribute],  "Value",SUMX(FILTER(ALL('Table'),'Table'[customer]=EARLIER('Table'[customer])&&'Table'[Year]=EARLIER('Table'[Year])&&'Table'[Attribute]=EARLIER('Table'[Attribute])),[Value]),
    "Rank",RANKX(FILTER(ALL('Table'),'Table'[customer]=EARLIER('Table'[customer])),[Year],,ASC,Dense))
var _table2=
FILTER(_table1,[Rank]<=2)
return
SUMMARIZE(
    _table2,[customer],[Attribute],[Year],[Value])

True2 =
VAR _table1 =
    SUMMARIZE (
        'True1',
        'True1'[customer],
        'True1'[Attribute],
        "Year", "Status",
        "Value",
            IF (
                SUMX (
                    FILTER (
                        ALL ( True1 ),
                        'True1'[customer] = EARLIER ( 'True1'[customer] )
                            && 'True1'[Attribute] = EARLIER ( 'True1'[Attribute] )
                            && 'True1'[Year]
                                = MINX (
                                    FILTER (
                                        'True1',
                                        'True1'[customer] = EARLIER ( 'True1'[customer] )
                                            && 'True1'[Attribute] = EARLIER ( 'True1'[Attribute] )
                                    ),
                                    [Year]
                                )
                    ),
                    [Value]
                )
                    <= SUMX (
                        FILTER (
                            ALL ( True1 ),
                            'True1'[customer] = EARLIER ( 'True1'[customer] )
                                && 'True1'[Attribute] = EARLIER ( 'True1'[Attribute] )
                                && 'True1'[Year]
                                    = MAXX (
                                        FILTER (
                                            'True1',
                                            'True1'[customer] = EARLIER ( 'True1'[customer] )
                                                && 'True1'[Attribute] = EARLIER ( 'True1'[Attribute] )
                                        ),
                                        [Year]
                                    )
                        ),
                        [Value]
                    ),
                UNICHAR ( 9650 ),
                UNICHAR ( 128315 )
            )
    )
RETURN
    UNION ( 'True1', _table1 )

2. Result:

Best Regards,

Liu Yang

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

[Anonymous]

Hello @sekhar438 ,

You can create a below measure that will return 1 or 0 based on condition and then can have conditional formatting applied based on the values. In the below case if it is 0 then green arrow as icon and if it is 1 then red arrow as a icon

Ordered Qty = SUM(Fact[OrderQty])

_ConditionalFormatting = 
VAR SelMinYearOrder = CALCULATE([Ordered Qty],date[The_Year]=MIN(date[The_Year]))
Var SelMaxYearOrder = CALCULATE([Ordered Qty],date[The_Year]=MAX(date[The_Year]))
RETURN
IF ( SelMaxYearOrder < SelMinYearOrder ,1,0)

Please let me know if it doesn't help out to resolve the problem.

[sekhar438]

Hi MuskanAgarwal ,

Thanks for your reply.

I have checked the above share calculation and getting the below output,

note: all values 

Required output:

In this table Years(latest last 2 years) are changed based on the Customer filter selection,

If Customer: 1002 then,

If Customer 1004

Thanks,

Chandrasekhar