cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mbixby
Frequent Visitor

calculate net change between periods

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 IDEmp StartEmp Exit
1June 2, 2019August 2, 2019
2June 6, 2019August 6, 2019
3June 11, 2019August 11, 2019
4June 16, 2019August 16, 2019
5June 21, 2019September 20, 2019
6June 26, 2019October 25, 2019
7July 21, 2019October 20, 2019
8August 21, 2019November 20, 2019
 

I need to calculate the final Net New column:

Employee Delta Active.PNG

 

 

 

 

 

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!

 

 

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

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.

View solution in original post

3 REPLIES 3
mbixby
Frequent Visitor

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
Community Support
Community Support

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.

View solution in original post

littlemojopuppy
Super User I
Super User I

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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors