Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kristir2010
Regular Visitor

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.

 

DateCategoryValue*What I want Max to be*
12/12/2022franchise100
01/04/2022franchise100
01/01/2017franchise140
03/09/2022franchise203
10/08/2022franchise233
1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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.

RicoZhou_0-1675235823856.png

 

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.

 

View solution in original post

1 REPLY 1
v-rzhou-msft
Community Support
Community Support

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.

RicoZhou_0-1675235823856.png

 

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors