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 a list of all equipment. The list is maintained outside of the project management software. Production version needs to be cleaned. The sample version contains clean data.

Timesheet Data

Daily time sheets that are submitted by PM, PC and or Site Supervisor into project tracking software.

The 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.

Target

The target table is a manual table. In here we use a general hours per week target for all equipment.

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_list, 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", "resource_type", "resource_class", "resource_category", "owner"}, {"asset_tag", "resource_type", "resource_class", "resource_category", "owner"}),
    #"Left Join Timesheet" = Table.NestedJoin(
        #"Expand Available Equipment", 
        {"asset_tag", "work_date"}, 
        timesheet_data, 
        {"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"}})
in
    #"Renamed Columns"

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}}
    ),
    #"Add Target Column" = Table.AddColumn(Source, "Target", each TargetTable / 7, Decimal.Type),
    #"Add Week Column" = Table.AddColumn(
        #"Add Target Column", 
        "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