Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Karim Mohsni]

Hi All
can someone please help me ?
I have a "pa2020900" pay table. which includes details by employee and by month. I want to have a column that has in front
each ID if he is hired this month or is going to leave
next month.
i tried many functions : CALCULATE; PREVIOUSMONTH; DISTINCTCOUNT but there's no results :( 
Thank you

——————————
Karim Mohsni
Auditor
——————————

[Shungu Dhlamini]

Hi @Karim Mohsni, how do you know (or how can you tell) if the employee is a new hire (was hired this month). Is it by using the date column. Also how can you predict the employee will leave. These conditions will assist in coming up with the measure.

——————————
Shungu Dhlamini
BI Analyst
shungud@gmail.com
——————————
——————————————-

[Karim Mohsni]

Hi Shungu Dhlamini

- A new hire = the ID exists in this month and does not exist in the previous month
- Departure is an ID that exists in this month and does not exist next month.
cordially

——————————
Karim Mohsni
Auditor
——————————
——————————————-

[Shungu Dhlamini]

, kindly attach your pbix file here with dummy data, if your data is sensitive, then we can take it from there. 🙂 ??

——————————
Shungu Dhlamini
BI Analyst
shungud@gmail.com
——————————
——————————————-

[Karim Mohsni]

Please find the attached file

——————————
Karim Mohsni
Auditor
——————————
——————————————-

[Shungu Dhlamini]

Hi please see attached which will hopefully point you in the right direction. I used two measures (Departures and New hires) , basically following similar logic. 2 sets of distinct IDs per dynamic month range and used the EXCEPT function to exclude ?rows which may be appearing in one table and not the other.

The result 

——————————
Shungu Dhlamini
BI Analyst
shungud@gmail.com
——————————
——————————————-

[Karim Mohsni]

Thank you for your precious help.
it’s Ok 🙂

——————————
Karim Mohsni
Auditor
——————————
——————————————-