Max filtered by a category and over time

[Teapotlid]

I’m new to PowerBI/DAX and want the max over both a criterion and over time but am having difficulty.

I have people's desired approximate weekly hours which they can flex by month (example Availabity table below). 

I want to show the max availability per person over a set period of time (e.g. a year / quarter).

I have a Date table, a People table and an Available table.

I built a bar chart using a Max by person measure (formula below) and hoped that a date range slider (from my Date table) could be used to filter it by year/quarter.

There's a 1 to many relationship between the People table and the Availability table (linked using fullname - not shown in the table below) and also 1 to many between the Date table and Availability table (linked using date shown in the Month column below).

Regardless of what I do with the Date range slider, I just see the max per person. 

I want Raj to show 40 in 2022 but 32 in 2023 and Penny to show 32 in 2022 and 40 in 2023 (I get 40 for each of them whatever the date range).

Any tips / ideas?

Many thanks in advance.

Name	Month	WklyHrs
Raj	1-Nov-22	40
Raj	1-Dec-22	40
Raj	1-Jan-23	32
Raj	1-Feb-23	32
Penny	1-Nov-22	20
Penny	1-Dec-22	32
Penny	1-Jan-23	40
Penny	1-Feb-23	40

Max of WklyHrs per Person=

MAXX(

    KEEPFILTERS(VALUES('People'[Name])),

    CALCULATE(MAX('Available'[WklyHrs]))

)

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Teapotlid]

Thank you Ashish, it works in a table where you can select a single year (and I love the simplicity) but it doesn't seem to work for a date range slider.

I applied it to my real data and, if people select a range that straddles years, it shows the sum of the max for those 2 (or 3 years). Using my example, if the date range slider is set to the range 1-Nov-2022 to 28-Feb-2023, Raj shows 72 (the 40 max for 2022 & the 32 max for 2023).

Is there any tweak that would fix it?

Many thanks in advance!

[Ashish_Mathur]

You are welcome.  Show the expected result very clearly.