cancel
Showing results for
Did you mean:
Super User

## Kaplan Meier Survival Curves

@Buzzshot43 posted a question on KM Survival Curves here:

https://community.powerbi.com/t5/Desktop/KM-Survival-Curve-in-Power-BI/m-p/326809#M145836

The post refers to the following Tableau article:

So, figured I'd give it a shot and have gotten part of the way there but was hoping the community could help me finish this off.

I am using the dataset from the Tableau article referenced above and am trying to recreate this using pure Power BI (no R). I imported the data as HRData. The data looks like:

ID,Name,Hire Date,Termination Date,Status,Department

1,Inez Laurie,4/23/2010,9/2/2015,Inactive,HR

2,Yasuko Scruton,1/20/2010,,Active,Sales

...

So, in the dataset, I created the following custom columns:

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

I created a Calendar table using

Calendar = CALENDARAUTO(12)

Which I tied to Termination Date (relationship)

I created a measure to give me a running total for Count:

Count running total in Date =
CALCULATE(
SUM('HRData'[Count]),
FILTER(
ALLSELECTED('Calendar'[Date]),
ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)
)
)

I created a measure for d_i:

d_i = SUM(HRData[Event])

I created a measure for n_i

n_i = COUNTROWS(HRData) + CALCULATE(COUNTROWS(HRData),ALL(HRData)) - [Count running total in Date]

This is where things get a little messy. The formula for KM goes something like:

(Previous Value of KM) * (1-d_i/n_i)

So, what I did was create three measures like this:

KM1 = 1-[d_i]/[n_i]

KM = [KM-1]*[KM1]

The thought here was that I could have a continuous line chart by [Date] in Calendar and calculate KM1 for that date, KM1 for the previous date (KM-1) and then use those to calculate KM.

But, I'm not really getting what I expected and was wondering if someone could take a look and see if they can get this to work or have a better way.

### I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Kaplan Meier Survival Curves

Full solution posted here:

### I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

10 REPLIES 10
Super User

## Re: Kaplan Meier Survival Curves

OK, I think I solved this or at least gotten very much closer. I just had a slap the forehead moment when I realized that what the KM formula calls for is an accumulated product. Duh!

So, what I did created the following custom columns in my Calendar table.

c_d_i = [d_i] //d_i measure

c_n_i = [n_i] //n_i measure

c_d_i/n_i = [c_d_i]/[c_n_i]

c_1-d_i/n_i = 1-[c_d_i/n_i]

Then I created the following measure:

KM running total in Date =
CALCULATE(
PRODUCT('Calendar'[c_1-d_i/n_i]),
FILTER(
ALLSELECTED('Calendar'[Date]),
ISONORAFTER('Calendar'[Date], MAX('Calendar'[Date]), DESC)
)
)

This *appears* to give me what I was looking for if I use Date as my X-Axis, I get a survival curve over time. I guess what I need to solve to get the same thing as the article is to get this for "Years" with the company, but I'm pretty certain I know how to do that with a SUMMARIZE. I'll update this once I get there.

### I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User

## Re: Kaplan Meier Survival Curves

OK, closer still, though I am not getting exactly the same results as in the original article here:

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!!

### I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User

## Re: Kaplan Meier Survival Curves

OK, as an 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.

### I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User

## Re: Kaplan Meier Survival Curves

Full solution posted here:

### I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Established Member

## Re: Kaplan Meier Survival Curves

Very interesting, thanks for posting this along with the solution

Frequent Visitor

## Re: Kaplan Meier Survival Curves

Wow. Thank you so much for your work and dedication @Greg_Deckler! It is greatly appreciated. Hopefully someday the capabilities of PowerBi will expand so measures and calculations such as these are easier!

Super User

## Re: Kaplan Meier Survival Curves

Yeah, I think the main stumbling point here is that EARLIER doesn't support a measure as a parameter, only a column. If it did support a measure, then it would be the equivalent of PREVIOUS_VALUE in Tableau and the two solutions would be nearly identical. That was the main hurdle that needed to be overcome.

### I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Member

## Re: Kaplan Meier Survival Curves

Hi Greg,

Firstly, just woww, this is really great work!,

An addition to this, Please can you help me to get the confidence Interval(Upper & Lower bounds).

Kind Regards,

Venu

Super User

## Re: Kaplan Meier Survival Curves

@Venusura  - I will look into what it would take to achieve that. Stay tuned.

### I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Announcements

#### New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)