Finding the second transaction date

DAX DAX Calculations
1

Hi there

I am trying to find the date of the second occurrence of a transaction (and then subsequently the third, forth, etc).

For example, a customer purchases various products on his first visit,
Product 1
Product 2
Product 3
on the 31/03/2017

But then the same customer purchases Product 2 on the 30/04/2017.
In a table that has the customer number, the transaction date, and the product, how do I get the subsequent sales date shown on the current sales record.
In the end, I want to be able to see for each record, when the next sale was of this product for this customer.

Customer Product Sales Date Next Sales Date
1234 Product 1 31/03/2017 31/07/2017
1234 Product 2 31/03/2017 30/04/2017
1234 Product 3 31/03/2017 12/12/2017
1234 Product 2 30/04/2017 06/09/2017
1234 Product 1 31/07/2017
1234 Product 2 06/09/2017
1234 Product 3 12/12/2017

Is this possible? So I need this to look at both the customer number and the product? Potentially, if I wanted to find this by location as well, could that be added in rather simply with an additional filter or condition?

Thanks,
Alan

2

Hi Alan,

Yes this is possible,

I used a similar technique when running cohort analysis during the recent learning summit.

See below for the formula technique.

Second Purchase Date = 
CALCULATE( MIN( Sales[OrderDate] ),
    FILTER( ALL( Sales ), Sales[OrderDate] > [Onboarding Date] ),
        VALUES( Customers[Customer Names] ) )
//looking the min sale AFTER the initial first sale

You see in this example I filter out the data before and on the last date.

This is exactly what you should do for the variety of calculations that you require.

1 Like