Forum Discussion

braybrookj's avatar
braybrookj
Icon for Advocate I rankAdvocate I
9 years ago
Solved

Time series help calculating measure

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

Need Help
  • braybrookj's avatar
    braybrookj
    9 years ago

    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

2 Replies

  • MalS's avatar
    MalS
    Icon for Resolver III rankResolver III
    9 years ago

    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's avatar
      braybrookj
      Icon for Advocate I rankAdvocate I
      9 years ago

      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