Highlighting the max and min values of a row in a matrix using Date table

[samkaluskar]

I have a sales table, a date table and a department table. I want to highlight the maximum and minimum sales amount across the selected dates by the departments.

I have the following sales table.

MonthYearDeptSalesMonth_Year

Month	Year	Dept	Sales	Month_Year
1	2020	A	10	2020.01
2	2020	A	20	2020.02
3	2020	A	30	2020.03
4	2020	A	40	2020.04
5	2020	A	10	2020.05
1	2021	A	2	2021.01
2	2021	A	30	2021.02
3	2021	A	40	2021.03
4	2021	A	50	2021.04
5	2021	A	10	2021.05
1	2020	B	20	2020.01
2	2020	B	30	2020.02
3	2020	B	100	2020.03
4	2020	B	120	2020.04
5	2020	B	80	2020.05
6	2020	B	90	2020.06
1	2021	B	40	2021.01
2	2021	B	60	2021.02
3	2021	B	90	2021.03
4	2021	B	80	2021.04
5	2021	B	100	2021.05
6	2021	B	40	2021.06

I also have a following date table:

MonthYearMonth_Year

Month	Year	Month_Year
1	2020	2020.01
2	2020	2020.02
3	2020	2020.03
4	2020	2020.04
5	2020	2020.05
6	2020	2020.06
7	2020	2020.07
8	2020	2020.08
9	2020	2020.09
10	2020	2020.1
11	2020	2020.11
12	2020	2020.12
1	2021	2021.01
2	2021	2021.02
3	2021	2021.03
4	2021	2021.04
5	2021	2021.05
6	2021	2021.06
7	2021	2021.07
8	2021	2021.08
9	2021	2021.09
10	2021	2021.1
11	2021	2021.11
12	2021	2021.12

Lastly, I also have a department table:

Dept

A

B

C

I am getting the following result:

I am looking to get the following result:

Following is my DAX code for highlighting min and max value (Note: I must use the Month_Year column from the date table):

MinMax = 
VAR Vals = 
    CALCULATETABLE(
        ADDCOLUMNS (
            SUMMARIZE ( Sales, 'Date'[Month_Year] ),
            "@SalesAmt", sum(Sales[Sales])
        ),
        ALLSELECTED ( 'Date'[Month_Year] )
    )
VAR MinValue = MINX ( Vals, [@SalesAmt] )
VAR MaxValue = MAXX ( Vals, [@SalesAmt] )
VAR CurrentValue = sum(Sales[Sales])
VAR Result = 
    SWITCH ( 
        TRUE,
        CurrentValue = MinValue, 1, -- 1 for MIN
        CurrentValue = MaxValue, 2  -- 2 for MAX
    )
RETURN
    Result

[amitchandak]

@samkaluskar , Create below measure and use in conditional formatting using field value option

MinMax =
VAR MinValue = calculate(MINX ( values('Date'[Month_Year]), calculate(sum(Sales[Sales]) )), ALLSELECTED ( 'Date'[Month_Year] ))
VAR MaxValue = calculate(MAXX ( values('Date'[Month_Year]), calculate(sum(Sales[Sales]) )), ALLSELECTED ( 'Date'[Month_Year] ))
VAR CurrentValue = sum(Sales[Sales])
VAR Result =
SWITCH (
TRUE,
CurrentValue = MinValue, "Red", -- 1 for MIN
CurrentValue = MaxValue, "Green" -- 2 for MAX
)
RETURN
Result