Hi All,
Please excuse the length of this post. I thought some explanation and background might be helpful
Background
I’m trying to replicate something I often do in excel for clients. For one client, (a scaffolding company), I have built a connection to the API of their job management software and I am now pulling their data out and warehousing it in a database where from where I can build PBI reports and dashboards for them that automatically update daily. Spreadsheets can therefore be done away with. What I am wanting to do is build a scaffold utilisation forecast that predicts when each job is going to end thus telling them when scaffold is likely to return to their yard.
Scaffold can be ‘counted’ in a number of different ways. A common way that this client uses is called Face Metres which is similar to square metres. The client knows how many Face Metres of scaffold they hold in total and they also know how much scaffold is on each current job as well as how much scaffold will go on pending jobs that have been won but not yet built.
By looking at the historic job length of each individual customer we can build up an estimate of when each job will likely end by taking the job start date and then adding their average historic job duration to that date to arrive at an estimated end date. For new customers where there is no historic averages, we use the average historic job duration of the Customer Group that the new customer fits into (commerical construction, residential house builder, maintenance company, painter, private home owner etc).
What I want to Achieve
The best way to display the forecast is on an area graph which allows the company to quickly and easily see how much scaffold they have remaining in their yard throughout the year so that they can make decisions around buying more/renting for short term lack of scaffold/hunting more jobs due to gaps in the future pipeline of work. The below image is an example of that:
The Problem
I am struggling to find the right DAX that will allow me to create a chart similar to the one above. All videos and examples I’ve seen never refer to how to continually include something (the face metres on current jobs) beyond its start date up until its estimated end date. In excel I do it with a series of “IF(AND(” statements for example for current jobs it would be “if the date in the table is within the start date and the estimated end date of each job, then add up the face metres of that job and add it to total scaffold out on current jobs for that date.”
Whilst the above is straightforward enough in Excel, I am not sure where to start in PBI with the DAX. There’s also the issue of more than one date. I have a date table in my model and I have established a relationship between that and the start date in my jobs table. The estimated end date is a DAX calculation Forecast End Date = SUM(‘Job Details’[Start Date]) + [Customer Avg Job Duration]
The model is still at the very beginning of its development. I haven’t brought in other tables like Customer Groups to establish average customer job lengths etc yet. But I am desperate to learn how I might be able to replicate a graph like this in PBI given the need to count the face metres over a range of dates between their start and estimated end dates and I have yet to find anything that might do this for me.
Any help would be gratefully received.
The early stage pbix file is attached.
Many thanks
Paul
Scaffold Utilisation Forecast.pbix (302.0 KB)