Forum Discussion
Using MAXX and filter with dates from date slicer
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 |
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.