# How Many Staff Do We Currently Have – Multiple Dates Logic In Power BI Using DAX

In this tutorial, you’ll learn how to work out the current staff population for multiple dates in Power BI using the COUNTROWS and FILTER function. You may watch the full video of this tutorial at the bottom of this blog.

This discussion is based on a question in the Enterprise DNA support forum.

A Power BI user wanted to calculate the number of staff members that have been hired within specific time periods.

For this problem, it’s important to understand how the data is set up. You should have the employee names and the dates when they started and left, among other details. These are common HR-related data.

To make this technique work, you must use inactive relationships and a type of formula that enables you to count the numbers between dates of the same row.

Table of Contents

## Staff Population Data And Model

Let’s look at the demo data.

You can see that the table uses staff reference numbers. They are useful because HR databases usually contain a lot of information.

There’s also the Start Date and End Date. The End Date returns blank if the person is still an employee of the company.

Now, to find out the number of employees for a certain time period, you first need to set up inactive relationships in your model.

When you have multiple dates in your fact table, you need to have inactive relationships so that you can work out the information or logic between specified dates.

For instance, if you want to calculate the number of staff that started within a specific month, you’d need an active relationship between the Date table and Staff Population table.

Using inactive relationships allows you to choose which elements in your facts and lookup table to turn on to make your calculation work.

## Choosing The Context

For this example, the dates have a month and year context.

You can make the table show a day context by simply changing the Values under the Visualizations pane.

This table can also be turned into a visualization:

The chart with a day context has a more granular level of detail compared to the one with a month and year context.

## Formula For Current Staff

Each result for the Current Staff row should be calculated individually.

You need to isolate each value and work through the logic behind it.

To understand this concept better, the row with May 2018 will be used as an example:

Now, to calculate the number of current staff, you need to use the events in a progress formula pattern:

### The COUNTROWS Function

The COUNTROWS function is counting the number of staff members within each row in the Staff Population table.

### VALUES Function And The FILTER Statement

Let’s now focus on the VALUES functions and FILTER statements inside the formula.

For the first logic, the VALUES inside the FILTER function iterate through every single Start Date in the Staff Population table:

If the Start Date is less than or equal to the MAX date, it will evaluate to TRUE.

For the May 2018 row, the MAX date is May 31. The first logic gives you a list of all the staff members who have start dates less than the last day of May 2018.

Let’s now look at the second logic in the formula:

It iterates through the end dates of each row in the Staff Population table containing start dates that have been retained after the first logic was run.

If the End Date is greater than or equal to the MIN Date, it will evaluate to TRUE.

For this example, if the end date is greater than the first day of May, the staff or employee still exists within that specified time frame.

### The ISBLANK Function

The ISBLANK function evaluates if the End Date is blank.

If the End Date returns blank, it means that the staff is still employed.

Thus, there are two conditions for the second logic in the formula. First, it needs to evaluate if the End Date is greater than or equal to the first day of the month; and second, if it’s BLANK.

After the formula iterates through those two logics, it then counts the remaining data in the Staff Population table.

The FILTER inside the CALCULATE function changes the context of the calculation.

## Conclusion

This tutorial covered how you can count the number of current staff within multiple dates using the COUNTROWS and FILTER function in Power BI.

To summarize, the FILTER statements build a virtual table containing data that fits the criteria being evaluated. Then, the COUNTROWS function counts the remaining data which gives the number of current employees within specific time periods.

Inactive relationships are needed to make this technique work. They make sure that you’re iterating through every staff member first, and then working through the filtered data. This method guarantees the correct staff count per date. An active relationship would’ve given the incorrect calculation.

Multiple dates scenarios can be very confusing when you’re staring out in Power BI. With this tutorial, you’ll have a better understanding of how to use this technique in your own models.

All the best,

Sam

[youtube https://www.youtube.com/watch?v=uWpwZMfP5ns?rel=0&w=784&h=441]

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

## How To Install DAX Studio & Tabular Editor In Power BI

In this tutorial, you'll learn how to download and install DAX Studio and Tabular Editor 3 in your...

## Creating A Date Table In Power BI The Fastest Way Possible

I'm going to show you a new approach in creating a date table in Power BI. You may watch the full video...

## Power BI Between Dates Working Hours Solution

In today’s blog, I will demonstrate an approach to Access Analytic’s Start Stop Challenge where, using...

## Building Your Data Model Relationships In Power BI

In today's tutorial, I want to highlight how important it is to build your data model relationships...

## How to Embed Power BI in Sharepoint: 4 Simple Steps

Embedding Power BI reports in SharePoint Online is a powerful way to display interactive data...

## Discover Unique Insights Using Power BI TOPN Function

In this video, I wanted to find an insight that was really unique, and hugely valuable for business....

## A New Look At CALCULATE – Power BI

In this tutorial, we'll be looking at how to use the DAX function CALCULATE in Power BI. You can watch...

## Ranking Visualization In Power BI – Dynamic Visual

In this blog post, I want to teach you how to show your customers moving through groups over time in a...

## Ranking Insights Using TOPN In Power BI

In this article, we will explore how we can use the TOPN function in Power BI. You will really enjoy...

## Data Storytelling And Important Questions To Ask

When it comes to data storytelling, there are three important factors to consider to make sure that our...

## Using Advanced Logic In Power BI To Correct Your Totals

Many times when you get into more advanced formula with DAX, you'll find that the totals that show up...

## Query Editor In Power BI | An Overview

In this tutorial, we’ll show a detailed example of why we always need to use the Query Editor in Power...