Forum Discussion
Calculating Net hires and leavers
Hi BI Gurus,
Could you please help me? I am trying to present the net hires and leaves per month in a combined graph.
The data is sourced from two different sources/tables. Right now it looks like the leavers are totalling all together for 17 each month.
Could someone please recommendf do I fix this?
CombinedHiresLeavers
In your scenario, since your "number of leavers" starts from June and "number of new hires" starts from Feburary, it's better to built the relationship to a date table first. And it looks like you are using measures to calculate the count of "new hires" and "leavers". You should use ALLEXCEPT() to have your calculation group on Month level. The formula can be like:
Count of Hired Date= CALCULATE(COUNTA(Table1[HiredDate]),ALLEXCEPT(Table1,Table1[Month]))
Count of Leavers= CALCULATE(COUNTA(Table2[End Date]),ALLEXCEPT(Table2,Table2[Month]))
If you still can't fix your issue, please share your .pbix with sample data.
Regards,
1 Reply
v-sihou-msftMicrosoft Employee
In your scenario, since your "number of leavers" starts from June and "number of new hires" starts from Feburary, it's better to built the relationship to a date table first. And it looks like you are using measures to calculate the count of "new hires" and "leavers". You should use ALLEXCEPT() to have your calculation group on Month level. The formula can be like:
Count of Hired Date= CALCULATE(COUNTA(Table1[HiredDate]),ALLEXCEPT(Table1,Table1[Month]))
Count of Leavers= CALCULATE(COUNTA(Table2[End Date]),ALLEXCEPT(Table2,Table2[Month]))
If you still can't fix your issue, please share your .pbix with sample data.
Regards,