cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Continued Contributor
Continued Contributor

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:

 

 

LastTransaction.JPG

 

 

~heathernicoale
4 ACCEPTED SOLUTIONS

Accepted Solutions
Super User I
Super User I

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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Community Champion
Community Champion

@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] )
)

View solution in original post

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/

 

 

 

View solution in original post

@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] )
)

View solution in original post

35 REPLIES 35
Super User I
Super User I

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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Continued Contributor
Continued Contributor

@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!!!

~heathernicoale
Continued Contributor
Continued Contributor

@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!!!

~heathernicoale

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!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

Microsoft
Microsoft

@heathernicole

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)

捕获.PNG

 

If you have any question, feel free to let me know.

Continued Contributor
Continued Contributor

@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

 

~heathernicoale

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Community Champion
Community Champion

@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] )
)

View solution in original post

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors