Forum Discussion

hprose's avatar
hprose
Icon for Helper I rankHelper I
3 years ago
Solved

Present in previous week but missing in current week

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.

 

idWeek
14/7/2023
24/7/2023
34/7/2023
44/7/2023
14/14/2023
34/14/2023
54/14/2023
64/14/2023
14/21/2023
34/21/2023
64/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!

Need Help
  • ThxAlot's avatar
    ThxAlot
    3 years ago
    Missing = 
VAR __wk = MAX( STAT[week] )
RETURN
    TOCSV(
        EXCEPT(
            CALCULATETABLE( VALUES( STAT[id] ), STAT[week] = __wk - 7 ),
            VALUES( STAT[id] )
        ),
        ,
        ,
        FALSE()
    )

5 Replies

  • ThxAlot's avatar
    ThxAlot
    Icon for Super User rankSuper User
    3 years ago
    Missing = 
VAR __wk = MAX( STAT[week] )
RETURN
    TOCSV(
        EXCEPT(
            CALCULATETABLE( VALUES( STAT[id] ), STAT[week] = __wk - 7 ),
            VALUES( STAT[id] )
        ),
        ,
        ,
        FALSE()
    )

    • hprose's avatar
      hprose
      Icon for Helper I rankHelper I
      3 years ago

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