cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Community Champion
Community Champion

Re: 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.

 



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

Proud to be a Datanaut!




View solution in original post

Highlighted
Community Champion
Community Champion

Re: Find Latest Transaction Date for Each Customer

@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

Highlighted
Community Champion
Community Champion

Re: Find Latest Transaction Date for Each Customer

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

Highlighted
Community Champion
Community Champion

Re: Find Latest Transaction Date for Each Customer in Quickbooks

@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
Highlighted
Community Champion
Community Champion

Re: 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.

 



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

Proud to be a Datanaut!




View solution in original post

Highlighted
Continued Contributor
Continued Contributor

Re: Find Latest Transaction Date for Each Customer

@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
Highlighted
Continued Contributor
Continued Contributor

Re: Find Latest Transaction Date for Each Customer

@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
Highlighted
Community Champion
Community Champion

Re: Find Latest Transaction Date for Each Customer

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




Highlighted
Microsoft
Microsoft

Re: Find Latest Transaction Date for Each Customer

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.

Highlighted
Microsoft
Microsoft

Re: Find Latest Transaction Date for Each Customer

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

Highlighted
Continued Contributor
Continued Contributor

Re: Find Latest Transaction Date for Each Customer

@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
Highlighted
Community Champion
Community Champion

Re: Find Latest Transaction Date for Each Customer

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




Highlighted
Community Champion
Community Champion

Re: Find Latest Transaction Date for Each Customer

@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
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors