Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have the following data on headcount.
Name | Position | Start Date | End Date | Current Status |
Joe Bloggsd | Head of Marketing | 21/02/2017 | Established | |
Noah Anyone | Head of Operations | 21/06/2017 | 20/07/2019 | Left |
Joe Bloggs Jnr | VP Marketing | 21/07/2018 | 20/01/2020 | Transfer Out |
I want to be able to calculate the number of employees we had at a particular date.
This would ideally be one filter or something that enables me to select a date from and it would give me the total number of employees at that date, that includes those who are currently established, who had just started, who have since left etc. I.e. just a date filter that enables me to say - So at 31st March 2019 how many people did we have in the business, where were they and what were they doing?
Easy enough for those established as case of counting them but almost need a time travel ability to look back and say - on X Date we had A established, B had yet to start etc. - and need to include those who have since left but were established employees at that time.
That won't work as it needs to be able to have a particular date and then say on that date how many did we have in the firm (i.e. whose start date and end date, latter if applicable coincided with it).
ok in any case add a date dimension and add a slicer on the top with the date
Now you have your date you want to analyze with
CurrentDate=SELECTEDVALUE(YourDateTable[Date])
Now your measure will be
EmployeesAtThatDate=
Var CurrentDate=SELECTEDVALUE(YourDateDim[Date])
RETURN
COUNTROWS(
FILTER(Employees;Employees[StartDate]<=CurrentDate && Employees[EndDate]>=CurrentDate))
As I'm writing on the fly and I don't remember you mihgt have to use
EmployeesAtThatDate=
Var CurrentDate=SELECTEDVALUE(YourDateDim[Date])
RETURN
CALCULATE(COUNTROWS(Employees);FILTER(Employees;Employees[StartDate]<=CurrentDate && Employees[EndDate]>=CurrentDate))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |