The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hi,
I was wondering if it's possible to recreate this guide and functionality in PowerBI:
https://www.linkedin.com/pulse/survival-curves-tableau-hr-data-chris-short/
Thank you.
Solved! Go to Solution.
@Buzzshot43 - I have the full solution published to a blog article here:
https://www.linkedin.com/pulse/kaplan-meier-survival-curves-power-bi-greg-deckler-microsoft-mvp-
@Buzzshot43 - I have the full solution published to a blog article here:
https://www.linkedin.com/pulse/kaplan-meier-survival-curves-power-bi-greg-deckler-microsoft-mvp-
Yes, that seems fairly basic. Will see if I can mock up some data and recreate that in Power BI DAX but definitely possible.
@Buzzshot43, I posted this in another thread but figured I would post it here as well. The other thread is here:
This solution is close and I think is a viable way of solving this but I'm not getting exactly the same results so this may need some tweaking. Original article here: https://www.linkedin.com/pulse/survival-curves-tableau-hr-data-chris-short/
My ego would have me believe that the original article is in error, but that is probably not true and I have something off in my calculations, if someone could help me find where things are going awry that would be great!! 🙂
OK, so I start with the HRData table from the article mentioned above. I'd post it here but that would be too much data. Here are the first 10 rows:
ID | Name | Hire Date | Termination Date | Status | Department |
0 | 1/1/2010 | 1/1/2010 | Inactive | HR | |
0 | 1/1/2010 | 1/1/2010 | Inactive | Sales | |
1 | Inez Laurie | 4/23/2010 | 9/2/2015 | Inactive | HR |
2 | Yasuko Scruton | 1/20/2010 | Active | HR | |
3 | Kristopher Walkes | 4/18/2010 | Active | HR | |
4 | Geraldine Mclennon | 6/12/2010 | Active | HR | |
5 | Pilar Willard | 1/14/2010 | Active | HR | |
6 | Candace Molton | 4/19/2010 | 4/4/2012 | Inactive | HR |
7 | Evelyne Kneeland | 4/22/2010 | Active | HR | |
8 | Elois Hires | 5/26/2010 | 8/11/2013 | Inactive | HR |
9 | Keeley Dewolf | 1/11/2010 | 5/27/2012 | Inactive | HR |
10 | Melisa Padua | 5/16/2010 | Active | HR |
So, I created a KM table like this:
KM = SUMMARIZE(HRData,HRData[Years],"Count",SUM(HRData[Count]),"d_i",SUM(HRData[Event]))
Then I added these columns:
Running = SUMX(FILTER(ALL(KM),[Years]<EARLIER([Years])),KM[Count])
n_i = [Count] + CALCULATE(SUM([Count]),ALL(KM)) - [Running]
d_i/n_i = KM[d_i]/KM[n_i]
1-d_i/n_i = 1-KM[d_i/n_i]
Then this measure:
MyKM = CALCULATE( PRODUCT('KM'[1-d_i/n_i]), FILTER( ALLSELECTED('KM'[Years]), ISONORAFTER('KM'[Years], MAX('KM'[Years]), DESC) ) )
You can then plot KM[Years] on the x-axis and MyKm on the y-axis in a line chart.
OK, so then I wanted to do this by Department, so I created a KMDept table like this:
KMDept = SUMMARIZE(HRData,HRData[DeptYears],"Years",MAX(HRData[Years]),"Department",MAX(HRData[Department]),"Count",SUM(HRData[Count]),"d_i",SUM(HRData[Event]))
And columns like these:
Running = SUMX(FILTER(ALL(KMDept),[DeptYears]<EARLIER([DeptYears]) && KMDept[Department]=EARLIER(KMDept[Department])),KMDept[Count]) n_i = [Count] + CALCULATE(SUM([Count]),ALL(KMDept),FILTER(KMDept,KMDept[Department]=EARLIER(KMDept[Department]))) - [Running] d_i/n_i = KMDept[d_i]/KMDept[n_i] 1-d_i/n_i = 1-KMDept[d_i/n_i]
And then a measure like this:
MyKMDept = CALCULATE( PRODUCT('KMDept'[1-d_i/n_i]), FILTER( ALLSELECTED('KMDept'[Years]), ISONORAFTER('KMDept'[Years], MAX('KMDept'[Years]), DESC) ) )
Then, you can plot KMDept[Years] on the x-axis and MyKMDept on the y-axis.
Again, the results are similar, but not 100% the same and not sure what can be done to improve the solution. I'll keep working on it but it's an interesting problem. And if anyone has a better way to solve it, I'm up for it!!
First, sorry, forgot to tell you the original columns I created in HRData table:
Years = IF([Status]="Active",DATEDIFF(HRData[Hire Date],TODAY(),YEAR),DATEDIFF(HRData[Hire Date],HRData[Termination Date],YEAR)) Event = IF(HRData[Status] = "Inactive",1,0) Count = 1
And, as a further update to this, first I forgot to mention that after you create the table, you need to relate the two tables together on Years or DeptYears. In addition, on a hunch I created a Days column in the HRData just like the Years column but with DAY specified in the DATEDIFF versus YEAR. I then created a KMDays table just like before and did all of the calculations using this table but everything is in Days and the two tables are related on the Days column. Here is the formula for the initial table.
KMDays = SUMMARIZE(HRData,HRData[Days],"Count",SUM(HRData[Count]),"d_i",SUM(HRData[Event]))
After you have that, just add the rest of the column and measure equivalents for that table. When I plot KMDays[Days] and MyKMDays measure, then I get what looks to be the exact results from the original article. At 372 days the measure is 80.26% which seems to be dead on to original article. I'm thinking its the ability of Tableau to generate a true continuous axis that is the main difference when doing this by Year.
User | Count |
---|---|
160 | |
110 | |
96 | |
86 | |
75 |
User | Count |
---|---|
157 | |
137 | |
132 | |
81 | |
61 |