Time series help calculating measure

[braybrookj]

Hi people!

I'm trying to calculate the number of staff employed at a given time.

I have a date dimension and a staff fact table.

To keep things simple for this the fact table has 3 columns: Person_ID, Start_Date, Leave_Date

I'm struggling to create a measure that tells me how many people I have on a given day.

So far I have created two relationships (inactive) between the two tables and created a "Starters" and a "Leavers" measure to tell me the movement over a given timeframe, however I can't get my head around how to create the "Total Staff" measure.

Any help greatly appreciated.

Cheers

[MalS]

You could use this as a starting point:

Staff Count = CALCULATE(COUNTROWS('Staff Fact Table'),'Staff Fact Table'[Start_date]<=DATE(2017,2,1),'Staff Fact Table'[Leave_date]>=DATE(2017,2,1))

This will give you the number of staff employed on the date you enter (in this case 1 Feb 2017). It assumes that the Leave_Date is set to some time in the distant future for staff who haven't left yet.

Some questions:

1. How did you want to specify the date? Did you want to use filters or slicers and pick, say, the max day from all of the selected dates?
2. How does your data represent the Leave_Date for people who haven't left yet?

[braybrookj · Accepted solution]

Hi @MalS

Thanks for your reply. I took some inspiration and created the below:

People - Headcount = 
IF(
	MAX('Date'[Month Start]) > TODAY()
	,BLANK()
	,CALCULATE(
		COUNT( People[FTE] )
	)
	- 		--SUBTRACT LEAVERS
	CALCULATE(
		COUNT( People[FTE] )
		,FILTER( People, People[Leave Date] < TODAY() && People[Leave Date] <> BLANK() && People[Leave Date] <= MAX('Date'[Month Start]) )
	)
	- 		--SUBTRACT FUTURE STARTERS
	CALCULATE(
		COUNT( People[FTE] )
		,FILTER( People, People[Start Date] > TODAY() || People[Start Date] > MAX('Date'[Month Start]) )
	)
)

It's a bit messy but returns the answer I am after.

The idea is that I would be able to use the date dimension and then plot graphs by month/day/week etc.

Cheers