Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
braybrookj
Helper I
Helper I

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

1 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

View solution in original post

2 REPLIES 2
MalS
Resolver III
Resolver III

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?

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.