Forecasting Recursive Measure

[irobba]

Hi there, hoping someone can point me in the right direction here. I am trying to calculate a predicted future value based on the previous year's numbers. Where an actual figure exists, it should be from the actual, where it doesn't, it should come from the previous year's predicted value. I've read a several very informative posts already on similar forecasting and recursive issues but either I'm not quite understanding correctly, or i can't quite bend them to my particular scenario and I think it is the combination of addition and multiplication in the forecasting operation that is stumping me. 

I've attached a picture of some dummy data to show what I'm trying to achieve.

I need to calculate future subscriber numbers from the last known actual count, based on a retention figure of existing subscribers, plus an intake of new subscribers. I need to do this in DAX because as you can see, the retention rate and new intake are to be parameters. 

Basic Formula is: ([Previous Year]*[Retention])+[New Subscribers]

For the purpose of illustrating my desired outcome, you can see I have used compound measures to step forward one year at a time from the last full year, but this a) doesn't feel like a good solution anyway and b), really starts to grind the gears on any real data if I get beyond 3 or 4 iterations which is too limiting. 

I'll be hugely grateful for any suggestions anyone can give me. 

Thanks in advance!

[v-yuta-msft]

Hi irobba,

"Basic Formula is: ([Previous Year]*[Retention])+[New Subscribers]", couldn't find column of measure like [Retention] from your pircture, could you clarify more details about your logic and the measure you are using? In addtion, maybe you can use forecasting feature in the analysis panel instead. Please refer to: https://docs.microsoft.com/en-us/power-bi/desktop-analytics-pane#apply-forecasting.

Regards,

Jimmy Tao

[irobba]

Hi Jimmy, thanks for your response.

I should have said [Retention Value] and [New Subscriber Value], they represent the selected value of the 2 what if parameters attached to the slicers in the bottom left. 

Thank you for the suggestion and the forecasting option is one of many things I have looked in to for this but unfortunately, I don't think I can use it with parameters in the way that I am looking to here for this kind of "what if?" analysis. I'm going to be using bookmarks to store the parameter states as scenarios effectively and using this for setting targets, rather than basing on past performance. 

So, to clarify the logic with an example, lets say my year end subscriber count for 2017 was 15000

Based, on retaining 90% of my subscribers each year and adding 1000 new subscribers

2018 Prediction = (15000*0.9)+1000 = 14500

2019 Prediction = (14500*0.9)+1000 = 14050

2020 Prediction = (14050*0.9)+1000 = 13645 

and so on.  So this is where the recursion comes in. 

I have tried a different approach today which is working better though I am still not entirely satisfied with it, to split the retained from base number and the rolling new subscribers in to 2 separate measures based on the number of years from my last count., then add them together. That makes the retained subscribers bit fairly straight forward but the new members bit is a bit ugly for my liking. In pseudo code...

Retained Subscribers = [Base Count]*[Retention Value]^[Years from Base]

Subscribers Added = IF([Years from Base]=1, [New Subscribers], IF([Years from Base]=2, ([New Subscribers]*[Retention Value])+[New Subscribers], IF([Years from Base]=3, ((([New Subscribers*[Retention Value])+[New Subscribers])*[Retention Value])+[New Subscribers])

and so on and so on, but I feel there should be a more elegant way of expressing this without needing a case for each year going forward. Maybe more of a maths question perhaps to express that as an iterative formula, but my maths isn't good enough!

Once again thank you for your response and for any help anyone can offer. 

[Anonymous]

@irobba  Was this ever solved for? Having a similar issue that previous posts have not solved.