cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Forecasting inflow

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!

0 REPLIES 0