Forum Discussion
Find Latest Transaction Date for Each Customer
I am trying to accomplish several things here - but the first goal is to determine the most recent or last transaction for each customer.
I have tried about 8 different things but fall a little short each time in accomplishing the real goal: The Sales info and the Customer are two separate tables - but have a relationship.
This is the most recent attempt in DAX:
Last Transaction = Filter(ALL(Customer[Customer Name]), Sales Detail[SalesTxnTimeModifed] = MAX(Sales Details[SalesTxnTimeModifed]))
But it gives this error: 'A table of multiple values was supplied where a single value was expected'
All I'm trying to do is generate a list of customers with their last transaction. From there I will look at a Rolling 11 months and a Rolling 13 months. When a customer goes 11 months without any activity - it will show in the report. And then when they go 13 months without any activty it will show in the 13 month report. But I have to have that list first.
Here's an example of the data I'm looking at:
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] ) )
36 Replies
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.
Last date worked for me! I've been looking for the solution for a few hours now and finally stumbled upon this. I added
Last Date = LASTDATE(Visits_Data[Date])
to a "New Column" so that i could use this data in a "Stacked Column Sheet" to show the last visit date on a job.
Thanks again!
how and where i should put this code ?
kcantor I was afraid I was over complicating it. :)
We've had to logout for the day for IT to work in the database so I won't be able to give this a go until Tuesday (unless I can log back in sooner).
I'll let you know! :)
THANK YOU!!!
kcantor I was afraid I was over complicating it. :)
We've had to logout for the day for IT to work in the database so I won't be able to give this a go until Tuesday (unless I can log back in sooner).
I'll let you know! :)
THANK YOU!!!
I hope it works, heathernicole, sometimes the easiest solution works. Other times you end up creating a new table and writing a novel lenght calculation.
Enjoy your long weekend!
Using RANKX could also help. Then you can find out the latest transactions(TransactionOrderByDateDesc=1) for each customer.
TransactionOrderByDateDesc = RANKX(FILTER(Table3,Table3[Customer]=EARLIER(Table3[Customer])),Table3[Date],,DESC,Skip)
If you have any question, feel free to let me know.
