Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mitig
Frequent Visitor

Count of Returnees for Retention

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_NUMYearQuarter_Code
0001201820
00022018

20

00032018

20

00042018

30

00022019

20

00032019

20

00052019

20

00012019

30

00042019

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 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Mitig ,

 

Please find the solution attached.

 

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Mitig ,

 

Please find the solution attached.

 

 

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

TomMartens
Super User
Super User

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:

image.png

 

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.