Most recent entry for a person

[Daretoexplore]

Hi all

I have some data which is customer and transaction based, like this.

CustID.         TransactionDate.         Amt

12345.          1/12/24.                      £32.45

12678.          1/12/24.                      £45.58

12345.          5/12/24.                      £24.62

(Just based on an example)

I'm looking to create a column which contains a '1' when it identifies the most recent transaction for each customer, son in the example above, it would have a column which would read 0 for the first row and 1 for the next 2.

Does anyone have any idea how to achieve this?

Many thanks.

[Bibiano_Geraldo]

Hi, @Daretoexplore ,

To create a column that identifies the most recent transaction for each customer, you can use the following DAX formula in Power BI:

RecentTransaction = 
VAR LatestTransactionDate = 
    CALCULATE(
        MAX('Table'[Transactiondate]),
        ALLEXCEPT('Table', 'Table'[CustId.])
    )
RETURN
    IF('Table'[Transactiondate] = LatestTransactionDate, 1, 0)

Now your table should look like this:

If this reply help you, please accept as solution and give a Kudo.

Thank You.

[Kedar_Pande]

@Daretoexplore 

 Create a New Calculated Column

IsMostRecentTransaction = 
VAR LatestDate = 
CALCULATE(
MAX('YourTable'[TransactionDate]),
ALLEXCEPT('YourTable', 'YourTable'[CustID])
)
RETURN
IF('YourTable'[TransactionDate] = LatestDate, 1, 0)

You should see a 1 for the most recent transactions for each customer and a 0 for others.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[johnt75 · Accepted solution]

You could create a column like

Is Latest Date =
VAR LatestDate =
    CALCULATE (
        MAX ( 'Table'[Transaction Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer ID] )
    )
VAR Result =
    IF ( LatestDate = 'Table'[Transaction Date], 1 )
RETURN
    Result

[FreemanZ]

hi @Daretoexplore ,

try like:

column =

VAR _firstdate

MAXX(

    FILTER(

        data,

        data[CustID]=EARLIER(data[CustID])

    ),

    data[date]

)

RETURN

IF([date]=_firstdate, 1, 0)