How to create calculated columns based on last year txn?

[lavdeepk]

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	Part-payment 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

[v-luwang-msft · Accepted solution]

Hi @lavdeepk ,

Try like below:

Step 1,import data,

Step 2, base on data ,create new column:

year = VALUE(RIGHT(TRIM('Table'[FY]),2))

No1 = RIGHT('Table'[Ref No_],6)

Step 3,if you want to create a new column ,use the below:

result =
IF (
    CALCULATE (
        COUNT ( 'Table'[No1] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[No1] = EARLIER ( 'Table'[No1] )
                && 'Table'[year] <= EARLIER ( 'Table'[year] )
                && 'Table'[year] > 'Table'[year] - 1
        )
    ) >= 2,
    "Partpayment",
    "No"
)

And if you want to create a new measure:

result2 =
IF (
    CALCULATE (
        COUNT ( 'Table'[No1] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[No1] = MAX ( 'Table'[No1] )
                && 'Table'[year] <= MAX ( 'Table'[year] )
                && 'Table'[year]
                    >= MAX ( 'Table'[year] ) - 1
        )
    ) >= 2,
    "Partpayment",
    "No"
)

Wish it is helpful for you!

Best Regards

Lucien