Please help me with the DAX formula to achieve desired results.

[Analystmate]

Hi All,

I need your help to create a calculated column based on conditions

Refer to the below sample data I have financial year-wise txn I want to categorize all txn based on condition. if any ref order exists in last year txn then mark it as "Partpayment"  Else "NO"  in the Part-payment Status field . (refer to desired results )

Please help me with the DAX formula to achieve desired results.

Ref No_                                FY         Status (desired results)

NK/20-21/011872             FY-20    NO

NK/20-21/006104             FY-20    NO

NK/19-20/146318             FY-20    NO

NK/20-21/002702             FY-20    NO

NK/20-21/001449             FY-21    NO

NK/20-21/014478             FY-21    NO

NK/20-21/006104             FY-21    Partpayment

NK/20-21/016876             FY-21    NO

NK/20-21/002702             FY-21    Partpayment

NK/19-20/134890             FY-21    NO

NK/20-21/000143             FY-21    NO

NK/20-21/008019             FY-21    NO

NK/19-20/106009             FY-22    NO

NK/19-20/083291             FY-22    NO

NK/20-21/000143             FY-22    Partpayment

NK/20-21/002906             FY-22    NO

NK/20-21/000292             FY-22    NO

NK/20-21/001449             FY-22    Partpayment

NK/20-21/006817             FY-22    NO

NK/20-21/014478             FY-22    Partpayment

Thanks

Lavdeep

[MFelix · Accepted solution]

Hi @Analystmate ,

Try the following calculated column:

Status =
VAR FY_Value = 'Table'[FY]
VAR Reference_Number = 'Table'[Ref No_]
VAR temp_table =
    SUMMARIZE (
        ALL ( 'Table'[Ref No_], 'Table'[FY] ),
        'Table'[Ref No_],
        'Table'[FY],
        "IDCOLUMN",
            'Table'[Ref No_] & "FY-"
                & RIGHT ( 'Table'[FY], 2 ) + 1
    )
RETURN
    IF (
        COUNTROWS (
            FILTER (
                temp_table,
                [IDCOLUMN]
                    = Reference_Number & "FY-"
                        & ( RIGHT ( FY_Value, 2 ) )
            )
        ) > 0,
        "PartPayment",
        "NO"
    )