Forum Discussion

Daretoexplore's avatar
Daretoexplore
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Most recent entry for a person

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.

Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    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

4 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    1 year ago

    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
  • Kedar_Pande's avatar
    Kedar_Pande
    Icon for Super User rankSuper User
    1 year ago

    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

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    1 year ago

    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.

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    1 year ago

    hi Daretoexplore ,

     

    try like:

    column =

    VAR _firstdate

    MAXX(

        FILTER(

            data,

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

        ),

        data[date]

    )

    RETURN

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