Forum Discussion
Historic Headcount numbers with a SCD2 HR Data Model - Sample Data Attached
Hi ayran,
In fact, these requirements can be achieved in Dax. You need to create and extract different range of records and saving these ID lists to different variables.
After these steps, you can use EXCEPT, INTERSECT functions to compare these lists. (create two variables with lists based filter on previous date range and current date range)
Logic :
EXCEPT fucntion with 'current' and 'previous': new employees.
INTERSECT fucntion with 'current' and 'previous' : old employees.
EXCEPT fucntion with 'previous' and 'current': left employees.Notice:
These operations required to loading large number of records and compare with them. So they will spend large amount or memory resource and case the performance issue when you try to execute this formula on a long date range.
EXCEPT function (DAX) - DAX | Microsoft Learn
INTERSECT function (DAX) - DAX | Microsoft Learn
Regards,
Xiaoxin Sheng
Hi ayran,
In fact, these requirements can be achieved in Dax. You need to create and extract different range of records and saving these ID lists to different variables.
After these steps, you can use EXCEPT, INTERSECT functions to compare these lists. (create two variables with lists based filter on previous date range and current date range)
Logic :
EXCEPT fucntion with 'current' and 'previous': new employees.
INTERSECT fucntion with 'current' and 'previous' : old employees.
EXCEPT fucntion with 'previous' and 'current': left employees.
Notice:
These operations required to loading large number of records and compare with them. So they will spend large amount or memory resource and case the performance issue when you try to execute this formula on a long date range.
EXCEPT function (DAX) - DAX | Microsoft Learn
INTERSECT function (DAX) - DAX | Microsoft Learn
Regards,
Xiaoxin Sheng