Forum Discussion

Elaina0907's avatar
Elaina0907
New Member
1 year ago

Need Help! - Custom Column for Repeating Values

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/IndexColumn AColumn BColumn CColumn D
1Headcount YearNext Headcount Year

Associate ID

Retained to Next Year?
2202320241001Retained
3202420251001Retained
4202520261001Not Applicable
5202320241002Retained
6202420251002Terminated
7202320211003Terminated

 

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!

Need Help

6 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    1 year ago

    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
     
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      v-aatheeque
      Community Support
      1 year ago

      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's avatar
        v-aatheeque
        Community Support
        1 year ago

        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!