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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
TSGD2123
Helper I
Helper I

Show values with one value in the past x weeks

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

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

1.png

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 )

View solution in original post

1 REPLY 1
tamerj1
Super User
Super User

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

1.png

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 )

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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