Number at a particular date

[tobiasmcbride]

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.

[v-easonf-msft]

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.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Mariusz]

Hi @tobiasmcbride 

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]

Hi @tobiasmcbride 

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

[tobiasmcbride]

Thanks for that however is saying that there's an expression error upon saving - OLE DB or ODBC error (we cannot convert the value null to type Date) - think it's because some of the end dates have 'null' in them so how is it best to fix this @Mariusz ?

[Mariusz]

Hi @tobiasmcbride 

Sorry for the late reply.

The function that is used in the attached file is converting nulls at end date to Today's date, or else you have nulls in the start date field ( this would be more likely a data quality issue ), it should work fine.

Are you connecting to the sql server?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[tobiasmcbride]

Hi @Mariusz 

Thanks for that. I do have some employees in this database with a null start date you are correct - these will be roles as yet not recruited for (i.e. we have a role outstanding but nobody yet confirmed for them and as such they have an undefined start date). Is there a way of altering that function to account for these people who have this undefined start date?

[Mariusz]

Hi @tobiasmcbride 

The easiest way would be to filter out these people ( start date <> null ) in a step before you invoke the function, as they have not had any working days there is no need for them to be in this table.

Please see the adjusted file on the attached.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[tobiasmcbride]

Hi @Mariusz ,

Looking at data again these are budgeted leavers (i.e. those we budget to leave this coming FY) this is what we have in the database with no start date but a particulary end date given our attrition modelling. What would you suggest to do as would actually like to keep these budgeted leavers in there but work out a way to tweak the formula to take account of null dates?

[Mariusz]

Hi @tobiasmcbride 

What would be their start date as an assumption? 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[tobiasmcbride]

At present the database assumes a non-existent one and not sure what we can do about that. Are there any tweaks we could make to ensure they still appear but to remove the null? Not sure what date we could use as a replacement here though instead of just having null? 

[tobiasmcbride]

Apologies @Mariusz forgot to tag you in reply above

[Mariusz]

Hi @tobiasmcbride 

In the attached file, I've added myself as an extra record, as you can see I'm still aspiring in the slicer however you will not be able to see me in the headcount by date because it would not make a logical sense.

If you need to count this individual separately, you can create another measure like below.

Headcount no start date = 
CALCULATE(
    COUNTROWS( employee ),
    ISBLANK( employee[Start Date] )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Mariusz]

Hi @tobiasmcbride 

Another way would be selecting a date point in the future by replacing start date nulls with let's say today + 30 and allocating this individual there.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[tobiasmcbride]

Hi @Mariusz 

trying to get this to work looking forward in time and seems to only be working from today backwards. We have some confirmed starters in the data that we can look forward and, hence, compare with budget. still trying to work out how to deal with the data quality matter around null start dates to get most accurate position so if any further ideas on this would be great. 

[tobiasmcbride]

Thanks @Mariusz managed to get it working eventually looking backwards from now.

I have budget headcount data too like so:

Budget	Date	Headcount Value
Latest Plan	31 October 2017	1101
Latest Plan	30 November 2017	1106

I want to display the chart with headcount data for the actuals and the budget line for 'latest plan'. However it seems to when displaying on the chart take the sum of all of these values for a particular time period so I get tens of thousands for the budgeted headcount and that remains fairly constant over time whilst the actual headcount data flucuates as you modelled. 

I think it has to do with the fact that the budget 'date' is only the end of the month or something but wondered if you could assist?

[tobiasmcbride]

@Mariusz wondered if you had any further thoughts on this regarding the error I am getting? 

[amitchandak]

Please refer to my solution on the same lines:

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Also, make sure your dates are detected as dates as they are in DD/MM/YYYY format.

[Anonymous]

You need a date dimension, look at my suggestion here

https://community.powerbi.com/t5/Desktop/measure-to-count-how-many-tasks-a-rep-completed-in-a-given-...