Need Help: Power BI filter with group by

[Anonymous]

Hi All,

I have an unique screnario which i am at wits end trying to resolve. The data looks like below, where each user has number of hours worked for a week. The hours can have any value greater than 0.

username	hours	start week	end week
A	40	7/4/2021	7/10/2021
A	36	7/11/2021	7/17/2021
A	34	7/18/2021	7/24/2021
A	30	7/25/2021	8/1/2021
B	50	7/4/2021	7/10/2021
B	20	7/11/2021	7/17/2021
B	20	7/18/2021	7/24/2021
B	20	7/25/2021	8/1/2021
C	28	7/4/2021	7/10/2021
C	32	7/11/2021	7/17/2021
C	44	7/18/2021	7/24/2021
C	50	7/25/2021	8/1/2021

I want to add a dropdown to the visual where the user can select a value for number of hours(5,10,20, 30,35 etc).

If the latest hour value(based on dates) is greater than the value selected, ignore that user.

If the latest hour value(based on dates) is less than value selected, select user and get end date of period where its higher. 

For e.g. if the user selects 35, the output will be :

	end of week
A	7/17/2021
B	7/10/2021

In the above, 

A

36

7/11/2021

7/17/2021

 row is higher than the 35 hour, so select that row and not the row above.

For B, a similar logic applies.

Please let me know if you have any ideas how to proceed with this. Thanks in advance.

[mahoneypat · Accepted solution]

Here is a measure expression that works with your username column in a  table visual, and a disconnected table with GENERATESERIES for the slicer.with

SlicerHours = GENERATESERIES(20,50,5)

Latest Week Over Limit =
VAR slicervalue =
    MAX ( SlicerHours[Value] )
VAR latesthours =
    LASTNONBLANKVALUE ( Hours[end week], CALCULATE ( MAX ( Hours[hours] ) ) )
VAR latestweekabove =
    CALCULATE ( MAX ( Hours[end week] ), Hours[hours] >= slicervalue )
RETURN
    IF ( latesthours >= slicervalue, BLANK (), latestweekabove )

Pat

[Anonymous]

Your are awesome! Much appreciated.