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 ，

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!