Forum Discussion

Mitig's avatar
Mitig
Frequent Visitor
6 years ago
Solved

Retention: Returnees Minus Completers Calculated

I am struggling with a retention measure where I need to exclude "completers" from the result. These completers are within a specific time range (08-20-2018 to 08-19-2019). So far, I am able to calcu...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Mitig ，

    I just updated your measure as below:

    1. Get ID Num for the next year and same quarter 

    2. Find the ID Num which both exist in current year and next year with same quarter, and exclude ID NUM which already completed

    Retained in Quarter_Code = 
var y=2018 
var q=20
var _ID=CALCULATETABLE(VALUES('TABLE1'[ID_NUM]),FILTER(ALL('TABLE1'),'TABLE1'[Year]=y+1&&'TABLE1'[Quarter_Code]=q))
return CALCULATE(DISTINCTCOUNT('TABLE1'[ID_NUM]), FILTER('TABLE1','TABLE1'[ID_NUM] in _ID &&'TABLE1'[Year]=y&&'TABLE1'[Quarter_Code]=q&&ISBLANK('TABLE1'[Completed_Date])))

    Best Regards

    Rena

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Mitig ,

The returned ID will be calculated group by year and quarter code just like below screenshot?

 

Best Regards

Rena

Mitig's avatar
Mitig
Frequent Visitor
6 years ago

Anonymous - The returned IDs would be only those color coded in green above (0002 & 0007). So only two IDs from the table above would be returned as "retained." 

 

The purple would all be filtered out regardless of if they were present in both "20" quarter codes in 2018 and 2019 because they completed. Given the completer status, 0003 would be excluded, even though they were present for both "20" codes in both years. 

 

The reds and blacks would not be returned either, as they were not present for both "20" quarter codes in both years. 

 

Apologies for any confusion, and I hope that helps! 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Mitig ，

    I just updated your measure as below:

    1. Get ID Num for the next year and same quarter 

    2. Find the ID Num which both exist in current year and next year with same quarter, and exclude ID NUM which already completed

    Retained in Quarter_Code = 
var y=2018 
var q=20
var _ID=CALCULATETABLE(VALUES('TABLE1'[ID_NUM]),FILTER(ALL('TABLE1'),'TABLE1'[Year]=y+1&&'TABLE1'[Quarter_Code]=q))
return CALCULATE(DISTINCTCOUNT('TABLE1'[ID_NUM]), FILTER('TABLE1','TABLE1'[ID_NUM] in _ID &&'TABLE1'[Year]=y&&'TABLE1'[Quarter_Code]=q&&ISBLANK('TABLE1'[Completed_Date])))

    Best Regards

    Rena

    • Mitig's avatar
      Mitig
      Frequent Visitor
      6 years ago

      Anonymous  -- Thank you very much for this! Though I am seeing some issues with my SQL-connected dataset, I am marking this as solved here. I am not fully there for my data model yet, but this has given me some insight on ways to troubleshoot the issue; if I cannot resolve, it would merit a brand new post. In any case, thank you again!