struggling with what should be simple. I have two tables ('crm_employees' and 'DimDates'). There exists a relationship between them (see picture below).
What I am trying to calculate is
This is my Measure for the active ones:
Active Employees = CALCULATE( COUNTA(crm_employees[Employee]); crm_employees[Status Code] = "Active" )
And this for the running total:
Active Employees running total in Date = CALCULATE( 'Key Measures'[Active Employees]; FILTER( ALLSELECTED('DimDates'[Date]); ISONORAFTER('DimDates'[Date]; MAX('DimDates'[Date]); DESC) ) )
Solved! Go to Solution.
Thank you for the advice. I tried it and do not get the correct result, either. What I do get is the number of employees, who left in a specific period (e.g. month).
As I have to deliver the report today, I resolved to take a different approach, showing the number of joiners, leavers and overall evolution of active personnel as in the chart below.
@rl_evans Just to clear it up, the 1753 date gets converted in SQL to blanks, for some reason Navision data directely in the database states it like that, apologise for me lazyness 🙂
The reason I suspected that, is because no matter what I try, my yearly stats gets messed up, and I cant figure out why.
Changing what you suggested unforntantly sill gave the incorrect results.
I see that you do take the acocunt for termintation dates in the future, correct ?
Im asking cause it seem that DAX calculation is exactly excluding all of current active employees with a termination date in the future, and cant figure out why that is.
@rado81 - My DAX formula calculates the number of active employees at the end of any given period. The initial Max date variables captures the max date given the current date context. Then, the CalculateTable function creates a table of all employees hired on or before the max date. The ALL function causes the CalculateTable to ignore the current date context. I'm doing this because the date context has a min and max date and I need to not only find all employees within the date context but also those hired before the date context; since those may still be active. The Calculate function looks for all active employees found in the intermediate table. Active employees are defined as those whose termination date is either blank or is greater than the max date of the period in context.
Hope this helps.
I don't think you can use the Active Status here as that one probably only works for the current Employees as of today right?
If you want historic "Active Employees" in a past month they need to have >= Contract Start and <= Contract End within that month.
Thank you. Yes, I think so, too and tried to do so. But so far without success. Any proposal, how the DAX would look like for this, would be much appreciated.
Not sure how this can be solved just by DAX.
I suggest you do a second Date Table joined on Contract End then you have Startdate and Enddate and then you can filter EndMonth <=
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.