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

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 2...

Need Help

Anonymous

3 years ago

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

How to apply conditional formatting for latest last years data in the matrix.pbix64 KB

Anonymous

Not applicable

3 years ago

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.