Interactive Forecasting

[pocquet]

Hi Guys,

I have two sets of data. One is Demand and the Other is Hours available to work on said demand.

I have produced a forecast in power BI for each (sadly on separate graphs, can these be combined somehow?)

What I want to be able to do is have a slider (the What IF function) that shows if I add x amount of hours then this will bring demand down by y.

I cant figure out how to do this?

Any ideas

Thanks

Poc

 

[macfuller]

You haven't included any fields or formulas. Does demand have more than one product or process? Is the relationship between demand and hours linear? Do wage rates apply and do you need to account for overtime/night/weekend differentials? Is this date bound - how far into the future do you want to predict and by hour, day, week, etc?

 

[pocquet]

Hi Mac, Sorry.

the forecast is out to 2 months and is at a weekly level.

This is actually super simple from a linear forecast POV and I have a model in excel.

It's more the how do I get it on power BI.

I have a number of procedures that take anywhere from 5mins to 500mins. I know what procedures I need to do that are on a waiting list each day.
Because I know how long each on average takes I know how long I need to clear my backlog.
Using this I predict the trends and can predict to procedure level. This forecast is currently going out to two months.

I also know my staffing levels and what hours are available to work on the procedures on the backlog.
However, this isn't flat and can vary.
Again this is at Daily level, Some days can have 0 hours, some can have 10+ depending on scheduling.

IN theory, I can see if we are reducing or increasing my waiting list however the first issue is that I can only project each metric on separate graphs, can these be combined with a forecast line?

The next task I want to do is add a slider to say If you add X hours of staffing levels what effect will that have on the backlog (ie up and down) and say the backlog will reduce, based on the forecast when will the two lines intersect.

I have the above working in excel, I just can't figure out how to put it into power BI using SQL query as a dataset.