Personnel Planning

For a company selling large projects involving a great number of project members with different roles, it is important to be able to plan how many people are needed at which time. This is, of course, not about the number of individuals; we do the analysis in FTEs (full-time equivalents) instead. (Besides, needing 2.5 FTEs sounds a lot better than needing 2.5 people, right?)

This chapter discusses an example of personnel planning for a project-based business. As you will see, only a few DAX measures are needed to compute the global need for personnel. The main complexity is in the large number of context transformations occurring during the calculation.

This chapter covers the following topics:

• The Power BI model for personnel planning
• Calculating sales, both order intake and projected sales over time
• Calculating FTEs needed for projects sold
• Optimizing the Power BI model
• Considering aggregation levels

The Power BI model

The analysis in this chapter is based on the model below.

You can download the Power BI model for this chapter, 2.8 Personnel Planning.pbix, from this link: https://github.com/PacktPublishing/Extreme-DAX/tree/main/Chapter2.8.

Figure 2.8.1: Power BI model diagram for personnel planning

The model contains two main fact tables:

• fProjectSales contains projects sold. The primary information in the table is the project type, which is the main characteristic of a project. The table also contains a Number column, which denotes the size of the project in terms of the project type. For example, a project can have type A and number 3, meaning that the project is comparable to three typical type A projects. The table contains the project budget as well.
• fFTE is a fact table with information about project types. It is an atypical fact table, as it does not contain any data that is strictly time-related. Instead, for each project...

Calculating sales

To start with a straightforward measure, let us calculate the total sales amount:

Total Sales = 
CALCULATE(
    SUM(fProjectSales[Budget]),
    USERELATIONSHIP(fProjectSales[StartDate], 'Calendar'[Date])
)

As USERELATIONSHIP activates the relationship between the fProjectSales table and the Calendar table on the StartDate column, this measure returns the amount sold in each month. While this is valuable information in terms of order intake, when working with projects that may span multiple years, another valuable insight would be to have the project budget spread out over the duration of the project.

There are several ways to do this. The easiest would be to divide the project budget by the duration of the project, and take that amount for each month that the project will run. This assumes that the income from the project will be evenly spread over the months.

While the duration of a project could be derived from the fFTE table, a more...

Calculating FTEs needed

Now that we have a view of the financial flows over time, let us turn toward analyzing the resources needed to staff the projects. As with the sales calculations, we will do this on a per-month basis, as the fFTE table provides this information by month.

The result we are aiming for should make it possible to create output like in the figure below:

Figure 2.8.6: FTEs needed by location, project, role, and month

For a few projects, you see the number of FTEs per role that are needed in each specific month. The calculation to achieve this is quite complicated, though the formula itself is not that long. We will go through it step by step.

Let us first start with two basic aggregations:

TotalFTE = SUM(fFTE[FTE])
TotalNumber = SUM(fProjectSales[Number])

Remember that the Number column denotes the size of a project relative to its type: for a project with Number = 3, we expect three times the FTEs associated with its project type...

Optimizing the Power BI model

While working your way through the measures discussed in this chapter, you may have noticed that we do a lot with the YearMonthCtr column in the Calendar table. Additionally, we do a lot of translations from a project's start date to the corresponding month counter.

Because of this, we have an opportunity to further optimize our solution, while making it easier to understand and maintain, by doing this translation beforehand. This is a case where a calculated column could indeed be appropriate – although it would still be good to consider creating this column using Power Query.

For now, let's create a calculated column in fProjectSales that contains the YearMonthCtr value corresponding to the project's start date:

StartCtr =
VAR ThisStartDate = [StartDate]
RETURN
CALCULATE(
    MAX('Calendar'[YearMonthCtr]),
    'Calendar'[Date] = ThisStartDate
)

With the new StartCtr column, many of the formulas...

Considering aggregation levels

The FTE calculation, as we have designed it, computes the FTEs needed at the level of project type. This means that we simply add the FTE numbers for all projects with the same type. It is a business consideration as to whether this is what the organization really wants.

For example, if an A1 project in Paris and an A1 project in London will run at the same time and each needs a 0.5 FTE project manager, we need 1 full-time project manager in total. While this is mathematically correct, you may argue that a single project manager will not be able to manage both projects. We may need boots on the ground, which practically excludes having someone travel between Paris and London all the time. The result would be that we need, in fact, two project managers.

The simple solution to this would be to do the calculation on a by-location basis, adding another iteration to the measure. However, even if we compute 0.5 FTE twice, the end result is still 1...

Summary

In this chapter, we have discussed ways to analyze the need for professionals to carry out projects. From a technical perspective, you have learned ways to work with multiple fact tables that must be considered in combination to provide useful results.

The challenge is not only to come up with correct results, but to find the optimal way to compute those results as well. Starting with a calculation per project, with an iteration over the fProjectSales fact table in our model, the next step is to consider possible ways to minimize the work done by not iterating over a fact table, but over either filter tables or a summarization of a fact table.

* * *

With this chapter, the book comes to an end. You have come a long way and seen many different business scenarios, as well as a diversity of DAX techniques. If we were to summarize the message of this book as a whole, and Part 2 in particular, a number of conclusions come to mind:

• The possibilities in what...