Show values with one value in the past x weeks

[TSGD2123]

Hello, I have a table like this:

ID    Connection     date

1     Connected        11-10-2022

1     Disconnected    12-10-2022

2     Connected        11-09-2022

3     Connected        13-10-2022

3     Disconnected    10-10-2022

4     Disconnected    11-10-2022

5     Connected        13-09-2022

5     Disconnected    10-10-2022

I need to show a table with just the IDs that hasn't been Connected for the past 3 weeks:

ID

2

4

5

Thanks a lot in advance

[tamerj1 · Accepted solution]

Hi @TSGD2123 
Please refer to attached sample file with the solution

Filter Measure = 
VAR NumberOfWeeks = 3
VAR LastDateAvailable = CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS ( ) )
VAR ReferenceDate = LastDateAvailable - NumberOfWeeks * 7
VAR FilteredTable = FILTER ( 'Table', 'Table'[Date] >= ReferenceDate && 'Table'[Connection] = "Connected" )
RETURN
    IF ( ISEMPTY ( FilteredTable ), 1 )