Forecasting inflow

o0llied — Sun, 12 Feb 2023 13:08:42 GMT

Hello everyone,

Thanks in advance for taking the time to help me!

I am looking to create some forecasting dashboard for the inflow into some classes.

For some context:
There is a course which people can join (also known as groups). Before this however, people need to take an interview. From historical data I have determined the % of people who actually start after this interview, and the amount of days it takes between the interview and the startingdate.

I would like to combine this with current data to forecast how full our future groups will be. To do this, i had to the following idea:

Determine the amount of people who currently have an interview planned. Multiply this with the % of people who start, and add the # of days it takes to start to the date of the interview. This will give an idea of when people would start, and how many would start.

In a seperate table I have the group numbers and their respective starting date (also for future groups). Based on the before calcultions I would like to generate a simple stacked column chart showing per group the amount of people who are already enrolled in a future group, and how many are expected to join each group.

This sounds relatively easy, I thought, however I am encountering a problem early on creating this visual.

For the expected starting date, I tried to create a new column in the table with the following formula:

Expected startdate = 'Applicants'[Interview date] + [Average throughput time]

This simply does not work. It results the same value as in 'Applicants'[Interview date]. Obviously I have done some research before writing this and have tried multiple variants. The [Average throughput time] measure is as follows:

Average throughput time = INT( CALCULATE( AVERAGE(Applicants[Days between interview and start]), Applicants[Days between interview and start] > 0 ) )

The format is set to whole number. The 'Applicants'[Interview date] has "date" as data type with "short date" as format.

I have also tried adding a column which just gives the throughput time, to add that to another column, but this gives a circular dependency. And calculating the average throughput time in a column in this table simply gives the amount of days for a row that has a interview date and a start date.

For the column i have also tried this to prevent it from not working due to empty rows (if applicants never started or never planned their interview):

Expected start date = IF('Applicant'[interview date] = BLANK(), BLANK(), 'Applicant'[Interview date] + [Average throughput time])

I have been able to create a measure that is able to succesfully calculate the amount of people that are supposed to start, based on the people who currently have their interview planned. However I am unable as of yet to then translate this to the groups they should start in. For this measure i use the following formula:

Expected amount of people to start based on currently planned interviews = Calculate( COUNT('Applicants'[Last known status]), 'Applicants'[Last known status] = "Interview planned", USERELATIONSHIP(Applicants[interview date], Calender[Date]) ) * [Conversion rate (%) interview to start]

For the groups, there is a seperate table called 'Groups'. The 2 columns of interest here are: 'Groups'[Groupname] and 'Groups'[Startdate].

The above is somewhat simplified, as applicants go through 3 interviews before starting. But I guess if i get this step to work, i can do the same for the people in the other 2 steps.

I hope I have given enough context of what I am trying to achieve, and what I have tried to do. I am looking forward to seeing what you guys and girls can come up with 🙂

Thanks a lot for the help in advance!

topic Forecasting inflow in DAX Commands and Tips

Forecasting inflow