Good Day All,
I am struggling with adapting the Current Staff calculation I picked up from this YouTube video by Sam
I am working from excel files held in Sharepoint online. I have uploaded a “demo” model with the tables and relationships relevant to the calculation I am struggling with. SampleHR.pbix (141.3 KB)
I am provided with an employee census files that contains these relevant fields, among several others. One row for each employee:
Hire Date
ReHire Date
I am also provided a terminations report on a monthly basis which I also pull from a SharePoint folder that merges each individual month of data into the terminations table within PowerBI. This table is linked to the Census table using a 1:1 bi-directional relationship. From this table I add a custom column to the Census table, [EndDate], using: EndDate = Related(Terminations[TerminationDate])
I created a date table using CalendarAuto and added Month / MonthNo / Year columns.
All three dates in the Census table and the TerminationDate from the Terminations table are all connected with inactive links to the date table.
At initial trial, I had the need to modify the base formula from the YouTube video to function when an employee is hired and then terminates within the same month. The modification I made works in these cases.
My problem is that any re-hire is not counted, and I have not yet been able to figure out how to add this into the formula. The base formula is below, and my modification is in bold. A screen capture of my model is also below.
CurrentStaff =
CALCULATE(COUNTROWS(Census),
FILTER(VALUES(Census[HIRE DATE]) , Census[HIRE DATE] <= MAX(Dates[Date]) ),
Filter(VALUES(Census[EndDate]),OR**(AND(Census[EndDate] >= MIN(Dates[Date]), Census[EndDate]> MAX(Dates[Date]))**,ISBLANK(Census[EndDate]) ))
)
Any assistance in conquering this obstacle will be much appreciated; I have been struggling for many hours and just cannot seem to get there. The eventual goal for this report is to compare current staff to headcount needs and produce turnover ratio’s across time.
Thanks very much for posting both your PBIX along with a clear explanation of your problem - that makes it much easier for those on the forum to provide support.
From an initial look at this I can see why you’re having so much trouble getting the DAX to work. However, the primary problem here is not DAX, it’s your data model. The way you’ve got it structured relying on the two bidirectional relationships introduces critical ambiguity into the model. Any calculation relying on dates can now take one of two paths - the path from the date table directly to the table being referenced in the calculation, or indirectly from the date table through the other table into the target table via the bidirectional relationships. This sort of ambiguity produces unreliable calculations - sometimes outwardly absurd results, and worse sometimes reasonable looking results that are just incorrect.
Here’s a great article that explains this ambiguity problem and its implications in detail:
What you’ll need to do first is rework your current data model into a star schema with only one- to-many unidirectional relationships. If you haven’t already, I would very strongly recommend working through this course start to finish, which will cover the key modeling and transformation concepts needed to revise your data model:
Here’s another excellent article that augments Sam’s video above well, discussing the importance of the star schema data model in Power BI:
See how you go with this. My experience is that once the right model is established, the DAX usually falls into place pretty easily. Give a shout if you have any questions.
@BrianJ, Thank you so much for the response.
I will certainly investigate my data model for improvement. You are correct that the results can be deceiving, as the only perceived error in this report is the failure to include the rehire’s.
When I try to modify the relationship between the terminations table and the census table, I am “forced” to leave it 1:1 and multi-directional by PBI Desktop, so I’m not sure how I will eliminate that one as I need it to populate the EndDate column in the Census table…perhaps I can do this in the query editor and simply not load the terminations table at all. I’ll look for alternatives here also.
Thank you again for the response, much appreciated.
Some other things to consider as you rework your model:
your two tables with employee information contain a mix of fact and dimension elements. I would first create an employee dimension table, with the static and semi-static/slow changing elements (employee ID, name, department, job title, company, etc.)
I would look at merging and simplifying the remaining information into a single fact table, and potentially going with a “long and narrow” structure (lots of rows, relatively few columns) that is focused around employee ID (linking back to your employee dimension table above), then HR action type (e.g., hire, terminate, rehire, etc.), status and HR action date. This will allow you to shape your model into a classic star schema, which is optimal for Power BI and DAX.
from this structure, it should be relatively simple to calculate the most current status for each employee, and then to run counts over any period of the number of employees in active status (i.e., hired or rehired within that period). I’m confident that when you build your model this way the DAX will simplify dramatically (you may actually not need to do much DAX at all, since finding the most current status for each employee given the modified data model will be easy to do in Power Query).
Here are a link w/ a few other recent threads embedded where transformation into that “narrow and long” structure made the calculations much easier.
I am not all the way through the course you referenced, but drawing on my previous experience and giving the model proper attention I have ended up with this:
- that data model is now truly a thing of beauty. I think of DAX as the smartest kid in class, but who is really susceptible to peer pressure. If you give it a bad crowd to hang out with, it quickly turns into a juvenile delinquent. But paired with a good data model like that, it’s a straight A student.
Now if you want to really supercharge that model, replace your calendar table with @Melissa’s Extended Date table:
This will give you a ton of functionality to make time intelligence analysis easier and further simplify your DAX. (She and I will be starting a time intelligence video series centered around this extended date table next week on the Enterprise DNA YouTube Channel).
Good luck with the rest of your analysis! Give a shout if you have any other problems.
Thanks! I’ll do my best to muddle through from here.
I’ll likely grab the date table for the final solution once I work out the current staff over time calculations. I cannot use the initial one from the tutorial now as my model is way different now.
- that data model is now truly a thing of beauty. I think of DAX as the smartest kid in class, but who is really susceptible to peer pressure. If you give it a bad crowd to hang out with, it quickly turns into a juvenile delinquent. But paired with a good data model like that, it’s a straight A student.