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...
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

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