calculate net change between periods

[mbixby]

Hello, I am trying to answer the question "what is the net change in staffing from month-to-month".  Staff have a start date and an expected end date.  For any given month, some staff onboard and others exit.  So I have two problems to solve: what is the total active staff each month and what is the change from month to month.  I'm new to Power BI -  still plugging through the basics - and am finding this quite a challenge.  

Given the sample set:

Emp ID	Emp Start	Emp Exit
1	June 2, 2019	August 2, 2019
2	June 6, 2019	August 6, 2019
3	June 11, 2019	August 11, 2019
4	June 16, 2019	August 16, 2019
5	June 21, 2019	September 20, 2019
6	June 26, 2019	October 25, 2019
7	July 21, 2019	October 20, 2019
8	August 21, 2019	November 20, 2019

I need to calculate the final Net New column:

I have been able to calculate total active following guidance from this post but now I think structuring the measures this way may have put me at a dead end - I can't figure out how to get to the next step to calculate the differences between months.

I have a date table [Dates] with an active relationship between Emp Start and a inactive relationship betwen Emp Exit.  The Date table contains a MonthSort to display MMM-yy and sorts using an index column.

And now I am stuck - can this be leveraged to calculate deltas or do I need to shift to a different appoach?  Thanks in advance for any help - been spinning wheels too long on this!

[mbixby]

Thank you Zoe!  I was able to figure out a solution after reading littlemojopuppy's post - I realize I didn't quite 'get' the way DAX filters are applied.  Your solution uses SUMMARIZE and collapses the 3 measures I needed down to one.  Very nice 🙂  

Posting my solution in here for contrast:

There is a primary relationship between the Calendar date and Program Start date and a secondary relationship with Calendar date and Expected Exit date.

Total Active Staff = CALCULATE(
 COUNT([Staff ID]), 
 FILTER( ALL( 'Staff'), 
 Staff[Program Start] < MAX('Calendar'[Date]) && Staff[Expected Exit] >= MAX('Calendar'[Date]))) 

Then use PREVIOUSMONTH to get the prior count (this is the bit I was hung on - not seeing that PREVIOUSMONTH is bound within the context of the current filter and can be used to further filter other measures):

Total Active Month Prior = CALCULATE( [Total Active Staff], PREVIOUSMONTH('Calendar'[Date]))

The net staff is then just:

Net New Staff = [Total Active Staff] - [Total Active Month Prior]

[dax · Accepted solution]

Hi mbixby,

You could refer to my sample to see whether it work or not.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[littlemojopuppy]

You didn't include any details on table names, etc. so I'm making them up.  Adapt as required for your data model.

Three measures:

CurrentMonth = 
	TOTALMTD(
		COUNT(Staff[ID]),
		Calendar[Date]
	)

PriorMonth = 
	CALCULATE(
		CurrentMonth,
		PREVIOUSMONTH(Calendar[Date])
	)

MTM Change = CurrentMonth - Prior Month

Regarding "The Date table contains a MonthSort to display MMM-yy and sorts using an index column."...on the modeling tab, you can choose to sort MonthYear by another column.