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

Re: Find Latest Transaction Date for Each Customer

@Sean - hey! Smiley Happy 

 

Gave the Calculate solution a try - got a similar error:

 

A date column containing duplicate dates was specified in the call to function 'LASTDATE': this is not supported.

This doesn't make sense. They're transactions. One of our companies does several thousand transactions per day. They are going to be duplicate values but they're not the same transaction / customer. 

 

Not sure what the deal is. Trying a couple of different things now. 

 

~heathernicoale
Super User
Super User

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/

 

 

 

Re: Find Latest Transaction Date for Each Customer

@SeanHad to change SalesTxnTimeModifed to a Date data type. It was orginally a DateTime data type. 

 

I'm thnking that's what caused the issue. Smiley Happy 

 

On my way to phase 2 of this report. THANK YOU!

 

@kcantor - I believe your solution was correct too - I think the DATETIME was messing it up.

~heathernicoale

Re: Find Latest Transaction Date for Each Customer in Quickbooks

@Sean @kcantor -

 

Well - I got the function to work - but it's not pull the proper data. The items grouped in red are the same customer. What it appears to be doing is pulling EACH transaction for the customer; NOT the LAST transaction. So I'm not sure what it's doing. 

 

I've tried all of the suggestions from yesterday. None of them give errors but all of them are producing the same results it seems. I've also tried switching out MAX for LASTDATE.

 

Customer Activity.JPGCustomer Last Transaction

 

Any ideas or thoughts about what might be the problem? I'm not sure what to do. I've tried using the LAST transaction date, the LAST salesTxnTime created, Time modified - I'm thinking it has to do with how Quickbooks handles data but I'm not entirely sure. @arify @Eric_Zhang - any thoughts or input you have is greatly appreciated! Smiley Happy 

~heathernicoale
Super User
Super User

Re: Find Latest Transaction Date for Each Customer in Quickbooks

@heathernicole Are you using this as a measure or as a calculated column? I have one application of this where I add it as a calculated column in my customer table. that way is pulls a single date per customer from the sales fact table to the customer table. Perhaps this method would work for you?

Re: Find Latest Transaction Date for Each Customer in Quickbooks

@kcantor I am using a calculated column - not a measure. I wonder if a measure WOULD work better in this case. Although I'm not sure why it would. 

~heathernicoale
Super User
Super User

Re: Find Latest Transaction Date for Each Customer in Quickbooks

Maybe using this:

 

Calculated Column

 

CustomerLastDateTransaction = CALCULATE(LASTDATE('Transaction'[TransactionDate]);ALLEXCEPT('Transaction';'Transaction'[CustomerName]))

 

sol1.png




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

Proud to be a Datanaut!




Re: Find Latest Transaction Date for Each Customer in Quickbooks

@Vvelarde - Hey! Smiley Happy @Sean I believe also suggested this or something similar.

 

However - I'm still getting the same results. The box in red is all the same customer. Also - the Cutomer, Transaction Date are in separate tables. I don't know if that makes a difference or not. 

 

 

Customer Activity 2.JPG

~heathernicoale
Super User
Super User

Re: Find Latest Transaction Date for Each Customer in Quickbooks

Can share us the data model:

 

How many tables using for this and his relationship.

 

Customers

Transactions

anything else?

 

Tanks




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

Proud to be a Datanaut!




Highlighted

Re: Find Latest Transaction Date for Each Customer in Quickbooks

@Vvelarde  Customer Activity Data Model.JPGTables used in this report (Not able to show all columns)

 

Raw Data to work with if anyone wants it. I couldn't show customer Names though so I just did a count.

 

Transaction AgeCustomer Last TransactionCount of Customer NameCustomer Status
3307/7/2015 0:00391911-Month Customer
8821/1/2014 0:00391913+ - Month Customer
8771/6/2014 0:00391913+ - Month Customer
8761/7/2014 0:00391913+ - Month Customer
8751/8/2014 0:00391913+ - Month Customer
8741/9/2014 0:00391913+ - Month Customer
8731/10/2014 0:00391913+ - Month Customer
8701/13/2014 0:00391913+ - Month Customer
8691/14/2014 0:00391913+ - Month Customer
8681/15/2014 0:00391913+ - Month Customer
8671/16/2014 0:00391913+ - Month Customer
8661/17/2014 0:00391913+ - Month Customer
8631/20/2014 0:00391913+ - Month Customer
8621/21/2014 0:00391913+ - Month Customer
8611/22/2014 0:00391913+ - Month Customer
8601/23/2014 0:00391913+ - Month Customer
8591/24/2014 0:00391913+ - Month Customer
8561/27/2014 0:00391913+ - Month Customer
8551/28/2014 0:00391913+ - Month Customer
8541/29/2014 0:00391913+ - Month Customer
8531/30/2014 0:00391913+ - Month Customer
8521/31/2014 0:00391913+ - Month Customer
8492/3/2014 0:00391913+ - Month Customer
8482/4/2014 0:00391913+ - Month Customer
8472/5/2014 0:00391913+ - Month Customer
8462/6/2014 0:00391913+ - Month Customer
8452/7/2014 0:00391913+ - Month Customer
8422/10/2014 0:00391913+ - Month Customer
8412/11/2014 0:00391913+ - Month Customer
    

 

~heathernicoale