Need Help! - Custom Column for Repeating Values

[Elaina0907]

Hi all, I'm looking for assistance building a Custom Column. I have an appended report feeding into PowerBI that tracks our annual headcount as of Jan-1 of each year by Associate ID. If an associate is active with us for multiple years, they will return multiple rows, each under its own year and row index. 

My question is how can I create a Custom Column to accomplish the following:

IF 'Associate ID' value repeats from one Headcount Year to the next, return "Retained"

IF 'Associate ID' value does NOT repeat from one Headcount Year to the next, return "Terminated"

IF 'Headcount Year' = 2025 (or current year), return "Not Applicable" as retention to the next year cannot be calculated yet.

Here is an example of what my table looks like. The Column D in Red is what I am trying to produce.

Table: Annual Headcount

Row/Index	Column A	Column B	Column C	Column D
1	Headcount Year	Next Headcount Year	Associate ID	Retained to Next Year?
2	2023	2024	1001	Retained
3	2024	2025	1001	Retained
4	2025	2026	1001	Not Applicable
5	2023	2024	1002	Retained
6	2024	2025	1002	Terminated
7	2023	2021	1003	Terminated

Other Notes:

• I have the table in PowerQuery sorted as shown above. First by Associate ID - Ascending, second by Headcount Year - Ascending. 
• In Excel I accomplished this by using the following formula: =IF(C3=C2,"Retained",IF(A2=2025,"Not Applicable","Terminated"))

Thanks!

[v-yiruan-msft]

Hi @Elaina0907 ,

You can create a calculated column as below to get it, please find the details in the attachment.

Retained to Next Year? = 
VAR _aid = 'Table'[Associate ID]
VAR _hcyear = 'Table'[Headcount Year]
VAR _next =
    CALCULATE (
        MAX ( 'Table'[Associate ID] ),
        FILTER (
            'Table',
            'Table'[Associate ID] = _aid
                && 'Table'[Headcount Year] = _hcyear + 1
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( _next ) ),
        "Retained",
        IF ( _hcyear IN { 2025, YEAR ( TODAY () ) }, "Not Applicable", "Terminated" )
    )

Best Regards

[ryan_mayu]

@Elaina0907 

you can try this

Column =

if('Table'[Headcount Year]=year(today()),"Not Applicable", if (not(ISBLANK(maxx(FILTER('Table','Table'[Associate ID]=EARLIER('Table'[Associate ID])&&'Table'[Headcount Year]=EARLIER('Table'[Next Headcount Year])),'Table'[Headcount Year]))),"Retained","Terminated"))

pls see the attachment below

[amitchandak]

@Elaina0907 , You try out approch like

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q