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] ) )
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!
You'll probably get better performance if you do a Group By operation in the Query Editor when you're getting data. (In Query Editor right click Customer Name column, then in Operations pick Max, then in the column selector you can pick Transaction Date column). Depending on your datasource, this might download a lot less information from your datasource.
kcantor - Hello! Hope you had a good weekend!
I tried LastDate on the Transaction Date Modified - and this is the error I got
A date column containing duplicate dates was specified in the call to function 'LASTDATE'. This is not supported.The current operation was cancelled because another operation in the transaction failed.
If I use this formula on the Calendar Table - no problem
That is odd. I use it on a Sales Fact Table that has many duplicates. In fact, I use it on all of my models so that other users know how new the data is.
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] ) )
What if I need to create a column with last transaction date for each customer? LASTDATE('Sales'[OrderDate]) will give only a single date for all customers. Am I right?
For Example:
Mathew - 12/03/ 2019
John - 09/ 07/ 2018
Raju - 08/ 08/ 2019 etc
I tried:
CALCULATE(LASTDATE(SDimProCltDept[Admission]), SDimProCltDept[ClientSK] = Clients[ClientSK])
but throwing an error
