Forum Discussion

CatManKuhn's avatar
CatManKuhn
Helper II
3 years ago

Headcount Forecast - Recursive DAX Formula

Hi all, 

 

I am incredibly stuck and looking for assistance; I know Power BI doesn't have looping functionality at this time. I am trying to forecast headcount 24 months based on a 3 year average turnover rate. In order to do so, I reference the prior month's headcount and multiple by the 3 year average turnover rate. I then subtract this amount from the prior months headcount to get a forecasted headcount. I then do this same formula 24 times and use a final measure to combine all of these measures together so that I am not getting just one value for each month. A screenshot is provided below. I have limited this to 8 month forecast for simplicity purposes.

 

I apologize if this isn't clear - I am happy to help explain further! I am looking for any solution. It is okay if I have to do this outside of Power BI, but the challenge is that this formula is based off a DAX measure and I need this to work with various dimensions.

 

Thank you so much for your time!

2 Replies