Retention: Returnees Minus Completers Calculated

[Mitig]

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 calculate a basic retention amount, but removing the "Completed" group from the mix is throwing me off. 

The measure I need to conduct is basically as follows: 

Count of Returnees in Quarter_Code 20 2019 from the same quarter 2018 by ID number EXCLUDING those who "Completed" within 08.20.2018 to 08.19.2019. (See color coding below table.) 

Below is a sample table of data with color coding and sample outcomes below that. 

TABLE1

ID_NUM	Year	Quarter_Code	Completed_Date
0001	2018	20
0002	2018	20
0003	2018	20	05-25-2019
0004	2018	30
0006	2018	20	12-31-2018
0007	2018	20
0008	2018	20	05-25-2019
0002	2019	20
0003	2019	20	05-25-2019
0005	2019	20
0001	2019	30
0004	2019	30
0007	2019	20
0008	2018	30	5-25-2019

Color Coding: 

Red or Black = Does not count as "retained" since ID not in present in BOTH quarter 20 of 2018 & quarter 20 of 2019. (NOTE: 0001 is NOT counted due to returning in quarter code 30 of 2019 (but not 20). 

Green = Counts strictly as "retained" 

Purple = Counts as "Completed" due to graduating within the timeframe 8.20.2018 to 8.19.2019 . (NOTE: Though 0003 returned in 20 of 2019, this ID only counts as a completer in this dynamic -- NOT retained or both retained and completed.) 

Expected Outcome:

Quarter_Code 20 in 2018 = 5 (0001, 0002, 0003, 0006, 0007)

Retained in Quarter_Code 20 in 2019 (but not completed) = 2 (0002 & 0007)

Completed = 3 (0003, 0006, & 0008)

Given this, the measure should only return 2 retained. 

The DAX code I have so far for the measure is below: 

Retained_Excluding_Completed = 
    VAR
        Present_20_2018 = 
            SELECTCOLUMNS(Filter(
                TABLE1, TABLE1[Year] = 2018 && TABLE1[Quarter_Code] = 20),"2018 Rows",TABLE1[ID_NUM])

RETURN

    VAR
        Present_20_2019 = 
            SELECTCOLUMNS(FILTER(
                TABLE1, TABLE1[Year] = 2019 && TABLE1[Quarter_Code] = 20),"2019 Rows",TABLE1[ID_NUM])

RETURN

    VAR
        Completed = 
            SELECTCOLUMNS(FILTER(
                TABLE1, TABLE1[Completed_Date] >= DATE(2018,08,20) && TABLE1[Completed_Date] <= DATE(2019,08,19)),"Completed 2018",TABLE1[ID_NUM])

RETURN

CALCULATE(DISTINCTCOUNT(
    TABLE1[ID_NUM]),
        FILTER(
            TABLE1, STABLE1[ID_NUM]
                IN
                    Present_20_2018),
        FILTER(
            TABLE1, TABLE1[ID_NUM]
                IN
                    Present_20_2019),
        FILTER(TABLE1, [[[ADDITIONAL CODE NEEDED HERE TO EXCLUDE COMPLETERS???]]]...

You'll see that I (with the help of this community on previous steps) have created variables for 1) those present in Quarter_Code 20 of 2018, 2) those present in Quarter_Code 20 of 2019, 3) those who completed within the specified time frame, and 4) the start to an expression that only includes retained IDs who did not complete. Clearly, I am missing the expression that EXCLUDES the completers from this measure. 

Any help at all is so greatly appreciated! Also, if I am going about this a completely wrong way, any guidance is likewise appreciated. Cheers! 

[v-yiruan-msft]

Hi @Mitig ,

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

Best Regards

Rena

[Mitig]

@v-yiruan-msft - 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! 

[v-yiruan-msft · Accepted solution]

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]

@v-yiruan-msft  -- 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!