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.         ...
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
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.