Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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:
Thank you so much in advance for any help! Cheers
Solved! Go to Solution.
@Mitig ,
Please find the solution attached.
@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.
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |