Count of Returnees for Retention

[Mitig]

Greetings, 

I am struggling to work out a simple retention calculation that can be automated into the future (with perhaps simple updates as new years are added). 

I need to start with the numbers as follows: 

• Distinct count of IDs in Year 2018 and Quarter_Code 20
• Distinct count of IDs in Year 2019 and Quarter_Code 20
• A calculation of the percentage retained between those two time periods. 

At this stage, I am really struggling to get a distinct count by ID numbers who were present in one quarter and who were present in the same quarter of the following year; the issue I am facing is that these are pulled from within the same column but in different rows. There are no other data points that indicate that an ID number "left," such as a departure date, so the only three columns I can pull from are the ID code, the year, and the quarter code. 

I started with the DAX code below (for a measure), but it is not working I think because it pulls information from one single line, and the year code spreads across more than one line. Here is the code: 

Returnees Fall 2019 = CALCULATE(DISTINCTCOUNT(TABLE1[ID_NUM]),FILTER(TABLE1,TABLE1[Quarter_Code]= "20" && TABLE1[Year] = 2018), FILTER(TABLE1,TABLE1[Year] = 2019 && TABLE1[Quarter_Code] = "20"))

Below is a basic view of what my data looks like (TABLE1) with green highlights as the target desired results: 

ID_NUM	Year	Quarter_Code
0001	2018	20
0002	2018	20
0003	2018	20
0004	2018	30
0002	2019	20
0003	2019	20
0005	2019	20
0001	2019	30
0004	2019	30

In my mind, the basic logic is as follows: 

Retained = DISTINCTCOUNT IF an ID was present in 2018 AND quarter code 20 AND they were present in 2018 AND quarter code 20. 

Then, a simple calculation of  [Retained]/[Present in 2018 quarter code 20]

Desired results from the above table: 

• Present in 2018 quarter code 20 = 3
• Present in 2019 quarter code 20 WHO WERE ALSO PRESENT in 2018 quarter code 20 (or [Retained]): 2 (the green highlights; ID 0001 is not counted due to being in the other quarter code.) 
• Ending percentage: [Retained] / [Present in 2018 quarter code 20]] = 2/3 = 66.7% [edited because duh]

Thank you so much in advance for any help! Cheers 

[amitchandak · Accepted solution]

@Mitig ,

Please find the solution attached.

[Mitig]

@amitchandak -- Thank you for your elegant solution. I tested this, and it worked like a charm. Many thanks for your help. 

[TomMartens]

Hey @Mitig 

I created this measure:

ending percentage = 
var prevYear = MAX('retenained'[Year]) - 1
var idsCurrentQuarter = 
    CALCULATETABLE(
            VALUES('retenained'[ID_NUM])
            , ALL(retenained[ID_NUM])
        )
var idsQuarterPreviousYear = 
    CALCULATETABLE(
            VALUES('retenained'[ID_NUM])
            , ALL(retenained[ID_NUM])
            , 'retenained'[Year] = prevYear
        )
return

DIVIDE(
    COUNTROWS(INTERSECT(idsCurrentQuarter , idsQuarterPreviousYear))
    , COUNTROWS(idsCurrentQuarter)
) 

This allows to create this table visual:

I'm not sure about the expected result, as 2/3 results to ~0.67.

Nevertheless, I hope this provides what you are looking for.

Regards,

Tom