Forum Discussion
Find Latest Transaction Date for Each Customer
I have successfully used LASTDATE.
Customer Last Transaction = LASTDATE('Sales'[OrderDate])
Then I populate the rows with the customer ID. My sales table is matched to my customer table using the ID.
heathernicole I have not read the whole post but give this a try...
Last Transaction = CALCULATE ( LASTDATE ( 'Sales Details'[SalesTxnTimeModified] ), ALLEXCEPT ( 'Customer', 'Customer'[Customer Name] ) )Hello heathernicole! I don't know what to tell you???
I use this exact Measure to calculate Last Payment by Purchaser - and there are MANY MANY duplicate dates!
I just tried it and it works as a Calculated Column as well.
You know the May Update was released today - have you updated? (Even though this should not be the issue!)
https://powerbi.microsoft.com/en-us/blog/
heathernicole Try this...
Go to Modeling Tab => Click New Table button => type this...
Summary Table = SUMMARIZE ( 'Sales Details', 'Sales Details'[Customer ID], "Last Transaction", MAX ( 'Sales Details'[SalesTxnTimeModified] ) )
Have you tried doing what I said to get latest date for each customer?
arify -Hey! :) Yes, I did try. But it wouldn't work because Customer and the Transaction Dates are in separate tables. Or at least I couldn't see a way to make it work. It didn't give me the option to select a different table in Group BY.
Doesn't Transaction Dates table have information of which customer made that transaction? If not, how did you create the tables in your screenshots? Did you join them?
You can merge those 2 tables in Query Editor (you can use the buttons)
@heathernicole Oh I see, they have a relationship. In that case, in the Query Editor, Customers table should have a Transaction Dates column created for this relation, and that column should have Tables or Records. You can expand that column (click the button on top right corner of the column, pick your date column in there). Then you should be able to do the Group By operation.