Forecast Cancellations by Age to chart by year - DAX Measure

[Kerry_M]

I want to write a DAX measure that I can chart to show how many of our customers are likely to cancel over time based on their age. Historically there are key ages when our customers are more likely to cancel.

I have two tables: A table showing the PAST customers (FormerClientTable) and their age when they cancelled, and a table showing CURRENT customers and their current age.

I know that customers are most likely to cancel at age 24, 30, 65 and 75 and, using my FormerClientTable table I am able to calculate the likelihood of cancellation for any given age.

PercentageLikelihood = SUMMARIZE('FormerClientTable','FormerClientTable'[Age At Cancellation],"%",COUNTROWS('FormerClientTable')/COUNTROWS(All('FormerClientTable')))

I can figure out the answer for a SPECIFIC year, but what I want to do is create a measure that will do this for any year that is shown on the X axis of my chart.

Does anyone have insight or suggestions about how to do this?

Thanks

[Anonymous · Accepted solution]

"I want to write a DAX measure that I can chart to show how many of our customers are likely to cancel over time based on their age."

I don't quite understand what it is you really want to do... The meaning of the above statement is ambiguous, to say the least. Any customer is likely to cancel at any time, first of all, even though the probability changes with the customer's age (but this is a conditional probability: P(customer cancels in year Y | customer is x years old), many of these probabilities will be 0). Secondly, the number of customers that cancel in year Y+1 depends crucially on the number of customers that canceled in year Y. Therefore, I don't think your formulation makes sense. What you could show is something different. You could calculate the (un-)conditional probability that a customer will cancel before or in year Y. If you get this probability, you'll be able to say what will be the expected number of customers that will cancel up to a certain point in time and the expected number of customers that will not cancel up to that point.

I'd be curious to know what you really meant by your statement.

Best

Darek

[Kerry_M]

Thanks for your response Darek.  It was helpful for me in framing the question and helped me realize that I can't make a prediction with just the age information alone.

I do have the year that the client cancelled but even with that,I think I would need to pull together the whole data set  (current and cancelled customers) to see how many people I had of each age in each year and then see how many of those cancelled or stayed on and then calculate the annual percentage of people who leave and then the percentages of each age group (to  see if those numbers were relatively steady over time) and then write a measure to predict this going forward...all well beyond my skill level at this point in time so I think I will put this question to rest.