Present in previous week but missing in current week

[hprose]

Hi all,

I have data roughly in the below format. For each week, I want to get the count of IDs that were present in the previous week but are missing in the current week. In the example below, April 14th - 2 (IDs 2 and 4 are missing), April 21st - 1 (ID 5 is missing). I want to create a chart with this week over week numbers.

id	Week
1	4/7/2023
2	4/7/2023
3	4/7/2023
4	4/7/2023
1	4/14/2023
3	4/14/2023
5	4/14/2023
6	4/14/2023
1	4/21/2023
3	4/21/2023
6	4/21/2023

Please let me know how I can go about it. Note that I have other columns in the table and may need to apply some filters in the formula like name = "xyz" while doing this calculation.

Thank you!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[hprose]

Thank you for your help @Ashish_Mathur !

[Ashish_Mathur]

You are welcome.

[ThxAlot · Accepted solution]

Missing = 
VAR __wk = MAX( STAT[week] )
RETURN
    TOCSV(
        EXCEPT(
            CALCULATETABLE( VALUES( STAT[id] ), STAT[week] = __wk - 7 ),
            VALUES( STAT[id] )
        ),
        ,
        ,
        FALSE()
    )

[hprose]

Thank you @ThxAlot ! I used COUNTROWS instead of TOCSV to get the count instead of the list. Appreciate your help!