The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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 )
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 )
User | Count |
---|---|
10 | |
9 | |
6 | |
6 | |
5 |
User | Count |
---|---|
20 | |
15 | |
14 | |
9 | |
7 |