I have attached PBIX file as well as Excel spreadsheet. I am trying to create a measure which should show the number of new employees each month. I am trying to create a graph showing terminated employees (bar) and new employees in a line graph. So in the attached pbix you can see bar graph and I am struggling with line graph for new employees.
The new employee measure should be dynamic with the date filter (using EOM).
I am using End of Month date which I have to use because my all my other model which I have removed is using end of month date for any date range.
Just a brief about the data unique code id column is code assigned to that employee and if they leave and come back they will be getting same code however code will show 2 different code. Rest other columns are straightforward.
I have tried creating a similar measure as ceased employees which is in pbix but for some reason it doesn’t work, If I change the connection from ENd of month to Start date. NEw employee measure works and then ceased employee doesn’t work.
I had a look into it and think I can help you out here, but it’s not clear to me how to identify the new employees in your dataset, what conditions have to be met to consider someone a new employee?
Would that be Resigned = no, terminationd date = none, updated reasons = none?
3 conditions to meet exactly what you have mentioned
please disregard the resigned heading of the column that is actually “Current Employee” heading. Please update the heading.
1 condition Current Employee Yes
2 condition Employee Start date.(so i can show my month)
3 updated reson “blank”.
I restructured your model, you should not have a date filtering the date table filtering another date.
Please note I put the date table relationships to both Start date and Termination date into inactive.
In the measures I used USERELATIONSHIP to get the the different dates filterted depending on our needs. I manually filtered out the blanks in the visual then, I checked in the excel Sep.21 should show 7 new employees and it does.
Hi @Slipper
Thank you very much Seb. This is exactly what I was looking for, just change few things and added back EOM table and created the link so the filter doesn’t have multiples EOM, but it works perfectly fine. I am going to bring this in my final model and will let you know if I still see any issues.
Thanks again for your help mate, very much appreciated.
I do struggle with USERELATIONSHIP formula currently but will go through this again, just so I can cement the formula in my brain
