## Desktop

Frequent Visitor
Posts: 3
Registered: ‎06-07-2018

# Maximum date difference between consecutive purchases for a customer

Hi!

I am having troubles with calculating the maximum difference between two consecutive purchases for each customer. Note that this is not the same as date difference between first and last purchase.

I have two tables (Purchase_table and Customer_table) which are related via CustomerID. In the Purchase_table the same CustomerID's are existing multiple times. As shown in the example pictures, I would like to calculate values for the column Max_diff_betw_purch. In the example I manually inputted value "8" for customer "1001", since it is the date difference between his second and third purchase, which is bigger than the difference between his first and second purchase.

Any help with this? I would prefer to do this with Power BI Desktop in data mode by adding a new column (Max_diff_betw_purch).

Sincerely,

L

Accepted Solutions
Super User
Posts: 2,850
Registered: ‎09-27-2017

## Re: Maximum date difference between consecutive purchases for a customer

@Regulate

You can use this column in the Customers Table

```Max_diff_between_Purchase =
VAR temp =
CALCULATETABLE ( VALUES ( Purchase_table[PurchaseDate] ) )
VAR temp1 =
temp,
"Days", DATEDIFF (
[PurchaseDate],
MINX (
FILTER ( temp, [PurchaseDate] > EARLIER ( [PurchaseDate] ) ),
[PurchaseDate]
),
DAY
)
)
RETURN
MAXX ( temp1, [Days] )```
Super User
Posts: 2,850
Registered: ‎09-27-2017

## Re: Maximum date difference between consecutive purchases for a customer

@Regulate

All Replies
Super User
Posts: 2,850
Registered: ‎09-27-2017

## Re: Maximum date difference between consecutive purchases for a customer

@Regulate

You can use this column in the Customers Table

```Max_diff_between_Purchase =
VAR temp =
CALCULATETABLE ( VALUES ( Purchase_table[PurchaseDate] ) )
VAR temp1 =
temp,
"Days", DATEDIFF (
[PurchaseDate],
MINX (
FILTER ( temp, [PurchaseDate] > EARLIER ( [PurchaseDate] ) ),
[PurchaseDate]
),
DAY
)
)
RETURN
MAXX ( temp1, [Days] )```
Super User
Posts: 2,850
Registered: ‎09-27-2017

## Re: Maximum date difference between consecutive purchases for a customer

@Regulate

Highlighted
Frequent Visitor
Posts: 3
Registered: ‎06-07-2018

## Re: Maximum date difference between consecutive purchases for a customer

Perfect, thank you Zubair! Your column worked like a charm.

I really appreciate the help!

-L