Need help in DAX to get retention

[MrSam]

Hello Experts,

Need your help in getting the employee retention. In my model, unfortunately, I don't have an Employee Dimension table, therefore, this is not an option for me. I have tried to use DAX to get the desired output but not sure what is going on here. My task is to identify employees retained currently compared to a past period and report based on the past period dimensions (Dept, Country, etc.)
I have the sample PIB file and Data file including the desired output attached for your kind reference.
Sample files : https://drive.google.com/drive/folders/1qqap1uLbkKGQIZpHjnA_2vwdcj1Um5rp?usp=sharing 

Below is my DAX code. I have even tried to add LOOUPVALUE and some other methods, but all failed to provide me with what I wanted. 

#Retention =

Var _StartDate = EOMONTH(MAX('Date'[Date]),-2) --Get end of the month, 2 months back

-- Base Headcount Data - 2 Months back (Ignores Date slicer)

VAR _BaseHeadcount =

    SELECTCOLUMNS(

        CALCULATETABLE(

            'Headcount',

            ALL('Date'),  -- Ignores Date slicer

            'Headcount'[Month] = _StartDate,

            NOT 'Headcount'[Department] IN {"DeptD"}

        ),

        "Employee ID", [Employee ID]

    )

-- Current Headcount Data (Current Period)

VAR _CurrentHeadcount =

    SELECTCOLUMNS(

        FILTER(

            'Headcount',

             NOT 'Headcount'[Department] IN {"DeptD"}

        ),

        "Employee ID", [Employee ID]

            )

-- Find the retained employees

VAR _RetainedEmployees = NATURALINNERJOIN(_BaseHeadcount,_CurrentHeadcount)

RETURN

COUNTROWS(_RetainedEmployees)

Desired Output

Original Department	Count of Retained Employees
DeptA	6
DeptB	4
DeptC	3
Grand Total	13

 
What I get

[techies · Accepted solution]

Hi @MrSam please check this measure

Retention new =

VAR _StartDate = EOMONTH(MAX('Date'[Date]), -2)

VAR _BaseHeadcount =

    SELECTCOLUMNS(

        CALCULATETABLE(

            'Headcount',

            ALL('Date'),  

            'Headcount'[Month] = _StartDate

        ),

        "Employee ID", 'Headcount'[Employee ID]

    )

VAR _CurrentHeadcount =

    SELECTCOLUMNS(

        CALCULATETABLE(

            'Headcount',

            ALLSELECTED('Headcount')  

        ),

        "Employee ID", 'Headcount'[Employee ID]

    )

VAR _RetainedEmployees = INTERSECT(_BaseHeadcount, _CurrentHeadcount)

RETURN

COUNTROWS(_RetainedEmployees)

[MrSam]

Hi @techies Thank you.
I was able to get the code to work once I placed the filters using dimensions. However, to make the cross filtering work properly, I have to modify the second half of the code you have provided.

Retention new =

VAR _StartDate = EOMONTH(MAX('Date'[Date]), -2)----Get end of the month of 2 months back from selected month

Var _CurrDate = EOMONTH(MAX('Date'[Date]),0) ----Get end of the currently selected month

VAR _BaseHeadcount =

    SELECTCOLUMNS(

        CALCULATETABLE(

            'Headcount',

            ALL('Date'),  

            'Headcount'[Month] = _StartDate,

             Headcount[Department]<>"DeptD"

        ),

        "Employee ID", 'Headcount'[Employee ID]

    )

VAR _CurrentHeadcount =

            CALCULATETABLE(

            VALUES(Headcount[Employee ID]),

            All(Headcount),

            Headcount[Month]=_CurrDate,

            Headcount[Department]<>"DeptD"            

        )

VAR _RetainedEmployees = INTERSECT(_BaseHeadcount, _CurrentHeadcount)

RETURN

COUNTROWS(_RetainedEmployees)

[MrSam]

Hi @techies 
I'll take back my previous comment. I was able to get the filters added. I have to rely on the Departments from the dimension table to get it work. However, I had to do a modification to the second half of the code so it was working properly when cross filters were applied.

Retention new =

VAR _StartDate = EOMONTH(MAX('Date'[Date]), -2)----Get end of the month of 2 months back from selected month

Var _CurrDate = EOMONTH(MAX('Date'[Date]),0) ----Get end of the currently selected month

VAR _BaseHeadcount =

    SELECTCOLUMNS(

        CALCULATETABLE(

            'Headcount',

            ALL('Date'),  

            'Headcount'[Month] = _StartDate,

             Headcount[Department]<>"DeptD"

        ),

        "Employee ID", 'Headcount'[Employee ID]

    )

VAR _CurrentHeadcount =

            CALCULATETABLE(

            VALUES(Headcount[Employee ID]),

            All(Headcount),

            Headcount[Month]=_CurrDate,

            Headcount[Department]<>"DeptD"            

        )

VAR _RetainedEmployees = INTERSECT(_BaseHeadcount, _CurrentHeadcount)

RETURN

COUNTROWS(_RetainedEmployees)

[MrSam]

@techies  Thank you. This works but as soon as I introduce the filter to removed DeptD, it breaks. Any solution to include that filter too.

[MrSam]

@bhanu_gautam 
Thank you. However, I am getting error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

[bhanu_gautam]

@MrSam , Try using

DAX
Retention =
VAR _StartDate = EOMONTH(MAX('Date'[Date]), -2) -- Get end of the month, 2 months back
-- Base Headcount Data - 2 Months back (Ignores Date slicer)
VAR _BaseHeadcount =
SELECTCOLUMNS(
CALCULATETABLE(
'Headcount',
ALL('Date'), -- Ignores Date slicer
'Headcount'[Month] = _StartDate,
NOT 'Headcount'[Department] IN {"DeptD"}
),
"Employee ID", [Employee ID],
"Department", [Department]
)
-- Current Headcount Data (Current Period)
VAR _CurrentHeadcount =
SELECTCOLUMNS(
FILTER(
'Headcount',
NOT 'Headcount'[Department] IN {"DeptD"}
),
"Employee ID", [Employee ID],
"Department", [Department]
)
-- Find the retained employees
VAR _RetainedEmployees =
NATURALINNERJOIN(_BaseHeadcount, _CurrentHeadcount)

RETURN
SUMMARIZE(
_RetainedEmployees,
[Department],
"Count of Retained Employees", COUNTROWS(_RetainedEmployees)
)