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!
