Power BI Exchange

Expand all | Collapse all

Measure to compute availability of employees

Jump to Best Answer
  • 1.  Measure to compute availability of employees

    Posted 4 days ago
    I'm trying to compute utilization of available time using three tables. I have tables for the Employee, Timesheet Data, and a Date table that includes a Control value for holidays so that those dates can be removed; the examples below are truncated from the real tables I'm using, but should contain all of the fields that are relevant. What I want to be able to do is compute the total available dates for each employee, taking into account hire and termination dates, while excluding holidays so that I can use that figure in another measure; I also need to be able to sum up the available dates across multiple employees without losing responsiveness to employment and term dates of each employee. I'm part-way there but struggling to determine a path forward for the start and end dates that behaves properly.

    Employee Table
    ID No Hire Date Term Date
    1 1/1/2018
    2 1/1/2018 1/3/2019
    3 1/4/2019
    Date Table
    Date Control
    1/1/2019 Closed
    1/2/2019 Open
    1/3/2019 Open
    1/4/2019 Open

    Using the measure below, I can calculate the availability without respect to employment and term dates and everything seems to display correctly in some different visual types. I need some mechanism for excluding dates prior to the hire date and after the termination date, in a way that calculates correctly both for an individual employee and for a group of employees that may have different start and term dates.

    Capacity = COUNTROWS(CALCULATETABLE(Employee))*COUNTROWS(CALCULATETABLE(Date,Date[Control]="Open"))

    I had thought that I could use DATESBETWEEN to produce another filter when calculating the date table, but I don't see a way to reference those values on the Employee table, just other measures I wrote on that table- and without being able to see the results in a visual, I can't assess if this would even work correctly when reviewing the capacity of multiple employees. Any input is greatly appreciated!



  • 2.  RE: Measure to compute availability of employees

    Posted 3 days ago
    Would it not be easier to make this a calculated column within the Employee table? That way, you can utilize each employee's date fields to build your calculation with a Filter command. Something like:

    PerEmplCapacity = countrows(filter('Date', 'Date'[Control] = "Open" && 'Date'[Date] >= Employee[HireDate] && ('Date'[Date] <= Employee[TermDate] || IsBlank(Employee[TermDate]))))

    I made the assumption that a blank TermDate would include every open day since their HireDate; if you have a different value you can plug it in for the last clause.

    ------------------------------
    Daniel Armstrong
    Developer
    Calgary AB
    ------------------------------



  • 3.  RE: Measure to compute availability of employees

    Posted 3 days ago
    I had considered using a calculated column similar to that but then the results don't seem to respond to date filters on the report page- if I set a filter for the month of January, the timesheet data filters properly but capacity is still displayed as though for the entire year thus spoiling the downstream measures. If my date table grows (and it likely will), then it would be for the entire length of the date table. I don't think that calculated columns respond to slicers and other dynamic filters, so that's why I've been trying to write a measure instead. I did write in that column and take a look, the math generally appears to be correct but being able to filter the results is critical.


  • 4.  RE: Measure to compute availability of employees
    Best Answer

    Posted 3 days ago
    Okay, with that in mind, I found a way to generate this view for you in a measure, so that it updates based on the Date adjustments. It uses a dynamic Crossjoin though, so usage with massive data sets will be slow, so take caution when using it.

    Capacity = (countrows(filter(crossjoin('Date',Employee),'Date'[Control]="Open" && Employee[HireDate] <= 'Date'[Date] && ('Date'[Date] <= Employee[TermDate] || IsBlank(Employee[TermDate])))))

    ------------------------------
    Daniel Armstrong
    Developer
    Calgary AB
    ------------------------------



  • 5.  RE: Measure to compute availability of employees

    Posted 3 days ago
    That looks like it may be what I need, seems to work well with the data I have to hand. I appreciate the warning about dynamic cross join, I try to use that function sparingly so didn't think to use that here. I think I should be able to just extend out the date table into year 3000 or so to create a much larger cross join than I expect to ever need and do some load testing so I can assess just how soon this will blow up my dashboard, but I think I should be able to manage the total data size over time by simply purging old dates when they are no longer relevant.

    Thank you very much, I appreciate your input.