Working with Duration (Availability) in PowerBi

Question

I'm trying to create a line graph in PowerBI. What I am trying to plot is somewhat complex.

I have the following tables:

Staffing - this table describes staffing for every employee in the company. By "staffing" I'm referring to how their time is allocated. For example, Employee #7 is staffed in "Chicken Manufacturing" with a StartDate of 1/1/2016 and an EndDate of 1/10/2016

EmployeeID   Project                  StartDate       EndDate
5            Cutting Lemons          12/1/2015       12/31/2015
5            Chicken Manufacturing    1/1/2016        1/10/2016
6            Fishing Lobsters         1/2/2016        1/5/2016
7            Chicken Manufacturing    1/5/2016        2/1/2016
8            Drinking                 2/1/2016        null

I also have a standard Date dimension as well as an Employees table and a Project table. The Employees table has a row for every employee and the Project has a row for each activity.

I am trying to create a line graph that has dates on the x-axis and the line will show me how many employees are active at the given date. So for dates 12/1/2015 - 1/10/2016 Employee 5 should be counted as "Staffed" but on 1/11/2016, he should not be included in the total.

What I'm actually trying to do is calculate Availability and by that I mean, how many Employee hours are available on each day (I have a project called Available) so ultimately I will want to count the number of Hours rather than employees, but I think if I can get to work with counting employees, I shouldn't have too much trouble multiplying number of employees by 8 hours per day.

Well I have about 6 measures, none of which work. In the past when I've done cumulative totals I always just did something like CALCULATE( Staffing, DATESMTD('Date') ), but as I need daily totals, I've been trying variations such as Measure = CALCULATE( sum( Staffing[Availability Hours column] ), DATESINPERIOD( Staffing[StartDate], MAX(Staffing[StartDate]), 1000, DAY)) — SUMguy

alejandro zuleta alejandro zuleta · Accepted Answer · 2017-01-26T22:04:02

Try something like this:

Count of Emp =
CALCULATE (
    DISTINCTCOUNT ( Employee[EmployeeID] ),
    FILTER (
        Staffing,
        [StartDate] <= MAX ( 'Date'[Date] )
            && (
                [EndDate] >= MAX ( 'Date'[Date] )
                    || ISBLANK ( [EndDate] )
            )
    )
)

It is not tested but should work as long as you have relatinship between Employee - Staffing. Also be sure to use your date column in the Axis setting.

Let me know if this helps.

Working with Duration (Availability) in PowerBi

1 Answers