Forecasting Year Over Year

[grifdoogindoggy]

Hello, I am looking for a specific forecasting method. For context, I am working for a company that offers sports programming and education across many different winter sports. One of our goals is to figure out the total number of participants year over year and from there figure out new vs returning customers to find our retention numbers. We have succeeded in this, as you can see in the graph below.

I want to take this to the next level by adding some "what if parameters." I want to be able to forecast until 2030 (or a year of the users choosing), a goal of how many athletes to have by that year, and a goal of overal retention for each year. What I'm hoping this will give us is an idea of how many new people we will have to target every year to both reach our retention goal and total number of participants. Ideally, I would like to see the graphs above along with the years ahead of our forecasting that will show number of retained and new participants, which will add up to the total number goal of the users choosing.

My current measures are below (please excuse weird naming conventions, I am working on two different reports right now that are very similar):

# Total Participants2 = DISTINCTCOUNT('UNION2'[ParticipantID])

# New Participants2 =

VAR vMinDate = MIN( 'Calendar'[Fiscal Year] )

VAR vPrevYear = Year(vMinDate - 1)

VAR vNewCustomers =

FILTER(

DISTINCT( 'UNION2'[ParticipantID] ),

CALCULATE(

ISEMPTY( 'UNION2' ),

vPrevYear <= Calendar[Fiscal Year],

Calendar[Fiscal Year] < vMinDate)

&&

CALCULATE(

NOT ISEMPTY( 'UNION2' )))

VAR vResult =

COUNTROWS( vNewCustomers )

RETURN

vResult

# Retained Participants2 = 'UNION2'[# Total Participants2] - 'UNION2'[# New Participants2]

Returning Customers2 =

COUNTROWS (

CALCULATETABLE (

VALUES ( 'UNION2'[ParticipantID] ),

VALUES ( 'UNION2'[ParticipantID] ),

FILTER (

ALL ( 'Calendar' ),

'Calendar'[Fiscal Year] < MIN ( 'Calendar'[Fiscal Year] ))))

Customers Last Year2 =

CALCULATE (

DISTINCTCOUNT ( 'UNION2'[ParticipantID] ),

FILTER (

ALLSELECTED ( 'Calendar' ),

'Calendar'[Fiscal Year]

= SELECTEDVALUE ( 'Calendar'[Fiscal Year] ) - 1))

RETENTION2 = 'UNION2'[Returning Customers2] / 'UNION2'[Customers Last Year2]

[Icey]

Hi @grifdoogindoggy ,

What's your data model? Please share us some sample data as what @lbendlin mentioned.

Best Regards,

Icey

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523