Forum Discussion
Helper IIINumber at a particular date
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.
22 Replies
You need a date dimension, look at my suggestion here
tobiasmcbrideThat 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 withCurrentDate=SELECTEDVALUE(YourDateTable[Date])
Now your measure will beEmployeesAtThatDate= 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))
- Mariusz
Community Champion
You can add an extra table to your model that expand dates ( start to end ).
On the attached, you will find a file that applies this concept to your sample.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
- tobiasmcbride
How do you expand those dates to get that as cannot see a formula anywhere in DAX for epandDates?
- Mariusz
In Query Editor you will see a function epandDates should really be expandDates (typo), this function is invoked in emplyeeDates table, please see the video.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
- Ashish_Mathur
Super User
- v-easonf-msft
Community Support
Hi , tobiasmcbride
Follow these steps:
1.Change the type of "Start Date" and "End Date "from "text" to "data" in "Edit queries"
1) replace "" to null in "End Date"
= Table.ReplaceValue(#"Changed Type","","null",Replacer.ReplaceText,{"End Date"})2)change type
= Table.TransformColumnTypes(#"Replaced Value",{{"Start Date", type date}},"ar-BH")= Table.TransformColumnTypes(#"Changed Type1",{{"End Date", type date}},"ar-BH")It will show as below:
2.Create calculate table "Date" and table "Current Status" to create slicer
Date = CALENDAR(MIN('Table'[Start Date]),TODAY())Current Status = SUMMARIZE('Table','Table'[Current Status])3.create measure "count" in Table and apply it in card visual
Count = CALCULATE(COUNTROWS('Table'),FILTER('Table',IF(ISBLANK([End Date]),'Table'[Start Date]<=[currentdate],'Table'[Start Date]<=[currentdate]&&'Table'[End Date]>=[currentdate])))4.create measure "visual control" and apply it in the table visual filter
Visual Control = IF([Count]>0,1,-1)In the end ,it shows as below:
Here is a demo .
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
