Determining Date of First Purchase from a List of Payments with payment dates

[Mike282]

Hi all,

Quick question. Is there a way to determine a date of first purchase based on a list of payments with payment IDs and Customer IDs? 

A customer has monthly payments recorded. Each payment has its own unique payment ID, the date of payment, the payment amount and the customer ID.

I want to find a way to determine the first time a customer has purchased the product whilst also excluding potential payments of 0 dollars as they might get a free month.

I've attached a PBIX file with my sample data 

I've tried using a calculated column with the following formula but it's causing a cyclical error. Any help would be greatly appreciated.

First Purchase Date = CALCULATE(MIN(Payments[Date]),DATESBETWEEN('DateDim'[FullDateAlternateKey],MINX(ALL('DateDim'),'DateDim'[FullDateAlternateKey]),MAX('DateDim'[FullDateAlternateKey])),Payments[Amount]>0)

[Ashish_Mathur]

Hi,

I cannot understand the result you want.  Do you want the result to be "Date of first purchase" or some amount.  Please show the exact result you are expecting in a simple Table format.  Once the figures are correct, we can switch the visual to whatever we want.

[Mike282]

Thanks @Ashish_Mathur 

I'm trying to flag the rows that are the first payment date for a specific customer (via the Customer ID). Something similar to the below:

So invoices 6 - 10 would be the date of first payment by the customer ID. Even though the same customers made a payment in June, it was 0 dollars so it isn't flagged as a first payment. Susequently payments for the month of August is flagged as FALSE because their first payment date was in July.

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

Column = [Date]=CALCULATE(MIN(Payments[Date]),FILTER(Payments,Payments[Customer ID]=EARLIER(Payments[Customer ID])&&Payments[Amount]>0))

Hope this helps.

[Mike282]

@Ashish_Mathur Thank you! That worked perfectly!

[Ashish_Mathur]

You are welcome.