May 12, 2020
I have a list of records of customer transactions spanning about 18 months, which i will continue to update with new data.
This list displays customers multiple times with the transaction type (code) and date of the transactions. e.g. one customer may have transacted five times in the past 18 months.
What I would like to show either in a table or pivot table is the most recent transaction for each customer and the related transaction type along with the salesperson.
e.g. Bob's last transaction was on 15/2/2020 for an item 732. I don't want to see Bob's previous transactions, just the latest one.
The result i'm looking for is a table of customers with their most recent transaction, the item and the name of the salesperson. Then i will add a follow up date, e.g. 3 weeks past that date.
I have created a measure in Power Pivot that I am using in a Pivot table to show the latest transaction for each customer, but I cant' figure out how to show the item for that transaction and the salesperson.
Latest Visit:=CALCULATE(LASTDATE('Paid Services'[InvoiceDate]),ALLEXCEPT('Paid Services','Paid Services'[FullName_ID]))
I would like to see... CustomerName | Last Transaction | Item | Salesperson