Forum Discussion
Continued ContributorFind 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] ) )
Continued ContributorSean - I've tried a couple of different solutions.
The Sales Details has all transaction information - that was what I was originally using. When that wasn't producing the correct results I attempted the Calendar - Transaction Date Table which is linked to the Sales Details table.
Sales Details is linked to Customer table with a Customer ID Link and the Calendar Table.
The Calendar table is simply a lookup table.
Information, such as the Customer Name are in the Customer Table - NOT the Sales Details table but they are related to each other.
Microsoft EmployeeHave you tried doing what I said to get latest date for each customer?
- heathernicole
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.
- arify
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)
- arify
@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.