topic Show values with one value in the past x weeks in DAX Commands and Tips

Show values with one value in the past x weeks

TSGD2123 — Fri, 14 Oct 2022 07:56:51 GMT

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:

Thanks a lot in advance

Re: Show values with one value in the past x weeks

tamerj1 — Fri, 14 Oct 2022 08:54:31 GMT

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 )