Need help with DAX Measure Average Over-time

[Anonymous]

Hello, I need help with this measure as I'm running into some issues. 

I have the following: 

1. Dates Table 
2. Active Employee Table [With Hire Date] 
3. InActive Employee Table [With Hire Date] 

I'm trying to write a dax that'll give me an average headcount in a given month. Any ideas how I can accomplish this? 

I'm attaching some screenshots for you to look at how the tables are configured. 

Both the Inactive and Ative tables are connected to the date table, using the Hire Date variable. 

Active Table 

InActive Table

[amitchandak · Accepted solution]

Calculate these two like these and add up. Assuming the Hire date is date table. I Assume the common date table here

Active Current Employee = CALCULATE(Count(Active[EE ID]), filter(all('Date'), 'Date'[Date]<=max('Date'[Date]))
Inactive Current Employees = CALCULATE(COUNTx(FILTER(Inactive,Inactive[Hire Date]<=max('Date'[Date]) && (Inactive[Termination Date]>max('Date'[Date])))
					,(Inactive[Inactive Id ])),CROSSFILTER(Inactive[Hire Date],'Date'[Date],None))

[Anonymous]

@amitchandak  This is producing excatly what I want! is there a way for us to exclude calculations on future dates with that dax? I noticed that based on the end date of my dates table, it's repeating the current number over and over and over and over. 

[amitchandak]

Put a visual or page level filter on the date table show <= today using relative or advance option. We can also add today to calculation as an additional filter <= today(), but it might not work in few cases.

[Anonymous]

That worked like a charm!!! Accpeting this as a solution. Thank You Much!! really apprecaite it. 

[amitchandak]

Please refer to the solution in blog. As you have two tables you have to do some changes.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Anonymous]

@amitchandak  thanks for that! It was helpful in other ways, but not for what I'm trying to achieve. 

@JarroVGIT  I appreciate your feedback!

As to your question, could you elaborate a bit on the two tables?

By the name I would expect only active employees in the 'Active' table? Yes, this is correct. 

Or is the first table actually all hire dates and is the second table all termination dates? No, first table is all active and second table is all inactive 

In that case, does that mean that for every row in the Active table, the EE ID is unique?  (and the same for InActive table?) Yes, EE ID is unique in both tables

[JarroVGIT]

Hi @Anonymous ,

Just an fyi: posting tables as pictures makes it incredibly (and unnessarely) hard for us to reproduce your usecase. Please consider posting copy-ready tables or attach an Excel/PBIX file via a cloud service as OneDrive or Dropbox.

As to your question, could you elaborate a bit on the two tables? By the name I would expect only active employees in the 'Active' table? Or is the first table actually all hire dates and is the second table all termination dates? In that case, does that mean that for every row in the Active table, the EE ID is unique?  (and the same for InActive table?)