Column Calc based on new

[M_SBS_6]

Hi, 

I have a sales table of which goes back 10 years. Within this I have:

cust_id

items

date

What I'd like is a Column which will output the year of their first purchase. 

If purchase_date was made in 2023 but no record of purchase before then, then 2023 if first purchase date was made in 2022 then 2022 etc. 

The difficulty I think is based on someone buying multiple items

Cust_id.    items.            date.       New col

1.              Football.       01/02/2024 2023

1.              Rugby ball.   14/09/2023 2023

1.              Hockey stick. 12/06/2023 2023

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

It is for creating a new column.

INDEX function (DAX) - DAX | Microsoft Learn

expected_result_calculated_column = 
YEAR (
    MINX (
        INDEX (
            1,
            data,
            ORDERBY ( data[date], ASC ),
            ,
            PARTITIONBY ( data[cust_id] ),
            MATCHBY ( data[cust_id], data[items], data[date] )
        ),
        data[date]
    )
)

[DemoFour]

You can try this as a calculated column @M_SBS_6  

First = 
CALCULATE(
MIN('Table'[Date]),
    FILTER(
        'Table',
        'Table'[Cust ID] = EARLIER( 'Table'[Cust ID])
    )
)