Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
pbi_learner_7
Frequent Visitor

Dynamic slicing of current employee records by date and category

I have a dataset of employee records. Each employee is one row; they each have columns for start / hire and end / exit dates, as well as some categorical characteristics like office location (one of five). There is a linked date table. 

 

I have a calculated measure for ‘current employees’ that counts the employees who are employed in a given month accurately based on their hire date and exit date. But since this is counting rows or IDs it does not preserve other information from other columns. I’d like to count people by office division, for example, on a monthly basis. And ideally this could work with a slicer, e.g. a slicer selecting month, or even a date range, and a chart of employees by office location that dynamically updates.

Conceptually, what I imagine is basically a dynamic column of T or F values that updates dynamically for each row (employee) based on an input of a date, to indicate whether they worked there at that time. And then to be able to filter only the T rows and count by categories. I realize this may not be (I think is not) possible, but that is my mental model of it. 

Is there a way of doing this without creating basically a new “current employees” count measure for each possible category?

Apologies if already answered elsewhere; I have not found a solution yet. 

1 ACCEPTED SOLUTION

Hi @pbi_learner_7 

 

Use a disconnected dates table and try this measure:

Employee Count = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        SUMMARIZE ( 'Table', 'Table'[Start Date], 'Table'[End Date] ),
        'Table'[Start Date] <= MAX ( Dates[Date] )
            && (
                'Table'[End Date] >= MIN ( Dates[Date] )
                    || ISBLANK ( 'Table'[End Date] )
            )
    )
)

 

danextian_0-1730705447199.png

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

6 REPLIES 6
pbi_learner_7
Frequent Visitor

Hey @danextian @FreemanZ - thank you for your replies! I think the issue I am having compared to the examples shared above is the additional of a categorical variable that I want to count by. 

A simplified and shortened version of the data for this example looks like this (it is fake data so not sensitive):

Employee IDStart DateEnd DateRace
77421/7/20149/8/2014White
77991/17/2014 Black
28841/26/20145/3/2014Asian
18822/15/201411/5/2015Other
44623/3/2014 Other
96884/6/201412/8/2016White
88714/10/2014 Asian
34885/23/20149/14/2015Black
66846/19/201412/9/2015Other
46606/28/201512/25/2016Asian
80718/14/201410/31/2016Black
95577/26/20168/24/2016White


We have a measure for current employees:

Current Employee = CALCULATE(COUNT('Employee info'[Employee ID]), FILTER('Employee info', 'Employee info'[Start Date] <= max('Calendar2'[Date]) && (ISBLANK('Employee info'[End Date]) || 'Employee info'[End Date] > max('Calendar2'[Date]))))

(referencing a date table called Calendar 2)

This seems to work for counting the employees at the end of each month, but not for counting by the categorical variable:

pbi_learner_7_1-1730652307445.png

I know we could create a measure that counts people in each category, but that seems inefficient, and there are also multiple other categories. It would be great to be able to filter / slice the count across multiple categories. Is there a way to do this more seamlessly? Thanks!

 



Hi @pbi_learner_7 

 

Use a disconnected dates table and try this measure:

Employee Count = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        SUMMARIZE ( 'Table', 'Table'[Start Date], 'Table'[End Date] ),
        'Table'[Start Date] <= MAX ( Dates[Date] )
            && (
                'Table'[End Date] >= MIN ( Dates[Date] )
                    || ISBLANK ( 'Table'[End Date] )
            )
    )
)

 

danextian_0-1730705447199.png

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Thank you @danextian - that worked! Although now I am feeling more confused than ever about date tables 😂

I thought linking a date table was necessary to provide a reference for the table of data, to ensure that all dates are represented. But using that date table to apply filter conditions does not work, I guess? So I also need a disconnected date table? Really appreciate the solution - would be curious about further context, perhaps if there's existing videos or articles covering this (I hadn't found any). Thanks!

You can still use a related dates table but you'll need to add a filter modifier. Assuming there is an active relationship between Dates[Date] and 'Table'[Start Date], your measure would be like this:

Employee Count = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table'[Start Date], 'Table'[End Date] ),
        'Table'[Start Date] <= MAX ( Dates[Date] )
            && (
                'Table'[End Date] >= MIN ( Dates[Date] )
                    || ISBLANK ( 'Table'[End Date] )
            )
    ),
    REMOVEFILTERS ( Dates )
)

danextian_0-1730778087492.png

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
danextian
Super User
Super User

Hi @pbi_learner_7 

 

As always, please provide a sample data that can be copy pasted (not an image) and your expected result from that - it can be a  link to an Excel file or a sanitized copy of your pbix. It is easier to provide a solution when there is data to work on.





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.