Equipment Utilization

About

The workbook that I am about to go through is a simplified recreation of the equipment portion of one of the weekly reports that were run and distributed at one of employments. The production verion queries directly to a SQL server using select statements. From there I futher refine the data in power query and create custom measures in power query.

The goal of this project is to report the reported equiment utilization from the field on given projects and compare that to a given target utilization for a given period of time. The report takes into account ownership of equipment and tracks equipment hours against all projects the pieces are used on. This ensures that any borrowed equipment is being reported and recognized as utilized.

Link to the file used for this demo Equipment Utilization

The Tables

Below I will briefly outline and expain the tables in the workbook coresponding data

Equipment List

Is an extraction from the equipoment list that is maintained outside of the project management software.

Equipment History List

Is a table that I maintain to track the equipment and its owners. The table is designed and maintianed by myself and the quipment manager. It is built with the intent that if equipent equiment changes ownershtip it will acuratly reflect the changes in reporting. The target field reprisents the target hours per machine. That way we can fine tune the hours as needed. Example the target hours for a packer or boring machine vs an excavator may vary drastically as a packer only sees limited use.

Timesheet Data

Daily time sheets that are submitted by PM, PC and or Site Supervisor into project tracking software. In production the data is queiried directly from the database.

The demo table Structure is as follows

Project Data

Information on the project including which business dimension is the owner.

Business Dimension

The list of Business Dimensions which equates to sections of the country.

Parameters

The parameters tab is where I store most if not all parameters to perform queries. In this example I use dates to store the parameters. I also use this page to store slicers and other items to filter the reports.

Power Query - Putting It All Together

Date Managagment

In power query I select the date parameters DateFrom and DateTo which are named ranges on the parameters tab.

DateFrom

= DateTime.Date(Excel.CurrentWorkbook(){[Name="DateFrom"]}[Content][Column1]{0})

DateTo

= DateTime.Date(Excel.CurrentWorkbook(){[Name="DateTo"]}[Content][Column1]{0})

Getting the dates in the period

let
    countDays = Duration.Days(date_to - date_from) +1,

    Source = Table.FromList(
        List.Dates(date_from, countDays, #duration(1,0,0,0)), 
        Splitter.SplitByNothing(), 
        {"work_date"}
    ),

    #"Transform to Date" = Table.TransformColumnTypes(Source, {"work_date", type date})
in
    #"Transform to Date"

Querying the data

Equipment Utilization Base

This report will serve as a base for the later summary report. This report catpures the fine grained detail of what pieces of equipment are being used where. We start by performing a conditional cross join with our equipment list to the list of dates taking into account the equipments in and out dates.

The ross join is achieved by utilizing the function ‘selectEquipmentByDate’ that is define at the start of my query. Then calling Table.AddColumn function. As the AddColumn iterates through the work dates the ‘selectEquipmentByDate’ function is called and the current work date is passed through. We then left join our timesheet onto the query we just performed.

let
    selectEquipmentByDate = (dateToCompare as date) => 
        Table.SelectRows(equipment_owner_history, each [date_in] <= dateToCompare and ([date_out] > dateToCompare or [date_out] = null)
    ),

    Source = Table.AddColumn(work_dates, "available equipment", each selectEquipmentByDate([work_date])),
    #"Expand Available Equipment" = Table.ExpandTableColumn(
        Source,
        "available equipment", 
        {
            "asset_tag", 
            "owner",
            "target"
        }, 
        {
            "asset_tag", 
            "owner",
            "target"
        }
    ),
    
    #"Left Join EquipmentList" = Table.NestedJoin(
        #"Expand Available Equipment", 
        {"asset_tag"}, 
        equipment_list, 
        {"asset_tag"}, 
        "equipment_list"
    ),

    #"Expand Equipment List" = Table.ExpandTableColumn(#"Left Join EquipmentList", "equipment_list", {"resource_type", "resource_class"}, {"resource_type", "resource_class"}),

    //Group timesheet to avoid any issues alter when joining due to multiple entreis for unit against a day
    #"Group Timesheet" = Table.Group (
        timesheet_data,
        {"workdate", "asset_tag", "project_code"},
        {"quantity", each List.Sum([quantity])}
    ),

    #"Left Join Timesheet" = Table.NestedJoin(
        #"Expand Equipment List" , 
        {"asset_tag", "work_date"}, 
        #"Group Timesheet", 
        {"asset_tag", "workdate"}, 
        "timesheet"
    ),
                            
    #"Expand Timesheet" = Table.ExpandTableColumn(#"Left Join Timesheet", "timesheet", {"project_code", "quantity"}, {"project_code", "quantity"}),
    
    #"Renamed Columns" = Table.RenameColumns(#"Expand Timesheet",{{"work_date", "Work Date"}, {"asset_tag", "Asset Tag"}, {"resource_type", "Resource Type"}, {"resource_class", "Resource Class"}, {"owner", "Owner"}, {"quantity", "Hours"}}),
    
    #"Table Buffer" = Table.Buffer(#"Renamed Columns" )
in
     #"Table Buffer"

The query ends with some clean up to rename the columns to be more readable to the end user. The Utilization Base is then added to the data model. I will linking it later so that we can make use of some global slicers that I have added to the workbook.

Equipment Utilization Report

The equipment Utilization report is a summary of the base, we remove all project related data and focus on raw hours entered. This is done by grouping the data by date, resource class,asset tag, owner, and suming the hours.

From there I add a column to get the target daily hours by dividing our weekly by 7. I also add a column for the start of the week, this column is based on a monday and used for our weekly views and reports.

let
    Source = Table.Group(
        equipment_report_base,
        {"Work Date", "Asset Tag", "Resource Class", "Owner"},
        {
            {"Hours", each List.Sum([Hours]), type nullable number},
            {"Target", each List.Sum([target]) /7, type nullable number}
        }
    ),
    #"Add Week Column" = Table.AddColumn(
        Source, 
        "Week", 
        each Date.StartOfWeek(
            [Work Date],
            1
        ),
        Date.Type
    ),
    #"Replace Null Hours" = Table.ReplaceValue(#"Add Week Column",null,0,Replacer.ReplaceValue,{"Hours"})
in
    #"Replace Null Hours"

I then add the table to the data model and later create some measures to calculate percentage based on target hours.

Power Pivot - Linking the Tables

Attached is the schema which we will use. For my weekly report we are mainly concerned with our region and will filter by our Business Dimension.

Adding the Percentage Measure

Percentage:=SUMX(FILTER(equipment_report_summary, equipment_report_summary[Asset Tag] = [Asset Tag]),[Hours]) / SUMX(FILTER(equipment_report_summary, equipment_report_summary[Asset Tag] = [Asset Tag]),[Target])

Finalaizing The Report

Last steps are to create the pivot tables and link the slicers. For this will omit the instructions on table and slicer creation.

Link to the file used for this demo Equipment Utilization