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!

[Anonymous]

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

[v-aatheeque]

Hi @Elaina0907 ,

Have you resolved the issue? If yes, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from the thread.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @Elaina0907 ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

[v-aatheeque]

Hi @Elaina0907 ,

We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

Thank you!

[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