I have monthly headcount data in one fact table and am trying to do joins using the Except function to find Inwards and Outwards by comparing the list of employees in each month. The function works great for finding the inwards, but when when the variables are switched for the outwards, the function does not work 100% correctly. I am looking to create a list of employees who came into the org in the current month and those that left in the prior month. The outwards calculates the correct total, but does not provide the correct detail of employees. It does not show all of them. I created the measure below, and switch the variables for the the Outwards. I am sure this is something simple, but I have not found anything yet. If anyone has any thoughts, I would appreciate it. I have attached the PBIX file too. Thanks.
Force Model Sample Data Only.pbix (74.8 KB)
Var EmployeesCM = Values ( ‘MR2020 Files’[NameID] )
Var EmployeesLM = CALCULATETABLE( Values ( ‘MR2020 Files’[NameID] ),
DATEADD ( ‘MR2020 Files’[DATA_YRMO_DT], -1, MONTH ) )
EXCEPT ( EmployeesCM, EmployeesLM ) )