DAX Except Inwards v Outwards

DAX DAX Calculations
1

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)

CMInwards =
Var EmployeesCM = Values ( ‘MR2020 Files’[NameID] )
Var EmployeesLM = CALCULATETABLE( Values ( ‘MR2020 Files’[NameID] ),
DATEADD ( ‘MR2020 Files’[DATA_YRMO_DT], -1, MONTH ) )

Return
Countrows (
EXCEPT ( EmployeesCM, EmployeesLM ) )

2

Hi @ealbrec,

The issue isn’t caused by the Measure but by the Model.

You are using time intelligence functions without a Date dimension table in your model. I added a Date table, marked it as a Date table and updated your measures and date slicer accordingly.

image
image809×431 17.5 KB

.
Please review the Designing advanced data models section, here:

.
I hope this is helpful. Here’s my sample.
eDNA - Force Model Sample Data Only.pbix (138.6 KB)

1 Like
3

Thank you so much! I will take a look and see if this works in the bigger model. I knew I had been having a date issue, but thought I had the formatting fixed to take care of it. I really appreciate the help!

4

Hi @ealbrec, Welcome to the forum! A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!