Hi Guys, I’m building a dashboard for our company about the utilization of construction equipment. Our company is active in earthworks and road construction and we have a lot or equipment (dozers, trucks, excavators) on our project sites. I want to report the sales, cost, utilization of our resources/equipment. To report the utilization I want to calculate the occupancy rate (= actual working hours/theoretical working hours). I want to report this per resource, and group of resources (all dozers, all trucks, etc.) over time (daily, monthly, quarterly, year). I would love to provide examples of my data with pictures, but somehow I don't see the uploading option on the forums anymore(?).. For this calculation I have 2 tables, a Date table and Sales order table 1. Date table. The date table has a column which shows if a date is a working day or not (working day = 1, weekend/holiday = 0) and the available working hours (8 hours per working day). Every resource has the same theoretical working hours per year/month etc. 2. Sales order table. The sales order table consists the billed hours (= actual hours) per resource per project per day. A resource can do multiple sales orders a day (e.g. a truck can do multiple assignments a day on different projects, so has it’s hourly rate billed to multiple projects on a single day). The occupancy rate is actual hours / theoretical hours. My first thought was to make a new column in the sales order table with theoretical hours (8 per day), and divide the actual hours column by the theoretical hours column. But off course then you get a wrong occupancy rate when a resource does multiple orders a day (e.g. 2 assignments of 4 hours on a single day, you get 8/16 = 50% occupancy, while it is 100%). I must create a measure which first calculate the actual hours for a resource on a single day, then divide this by the theoretical working hours for a single day. I also want to calculate this for group of resources (all dozers, all truck etc.) and on different time periods (daily, weekly, monthly, quarterly, etc). It looks simple when I state it like this, but I just can’t get my head around it..
