Using MAXX and filter with dates from date slicer

[kristir2010]

I want the max value of a category. Which I have done successfully by using this

Max =

var fid = Table[category]

return

    CALCULATE(

        MAXX(

            FILTER(Table, Table[category] = fid),

            Table[Value]

        )

    )

But I want to be able to compare the dates in my table to the current selected dates from my slicer. As in, I only want Max to be the maximum value within the date range I have. How can I differentiate the current row's date from the min and max date slicer selection? I keep getting circular dependency errors.

Date	Category	Value	*What I want Max to be*
12/12/2022	franchise1	0	0
01/04/2022	franchise1	0	0
01/01/2017	franchise1	4	0
03/09/2022	franchise2	0	3
10/08/2022	franchise2	3	3

[Anonymous · Accepted solution]

Hi @kristir2010 ,

You can try this code to create a calculated column.

Value in Max Date each Category =
VAR _MAXDATE =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Category] ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Date] = _MAXDATE )
    )

Result is as below.

Best Regards,
Rico Zhou

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