DAX for next purchase date

[elvincplee]

Hi, I'm going to calculate the next purchase date by DAX.

The background is that I have a table with purchase_date, customer, product, qty. I'd like to base on the same customer and product to calculate the next purchase date. The table should as follow.

Date	Customer	Product	Qty
5-Feb-22	B004	PC003	7
9-May-22	B004	PC003	12
25-Aug-22	B004	PC003	88
7-Nov-22	B004	PC003	23
8-Apr-22	D002	PB002	9
22-Jul-22	D002	PB002	46
21-Oct-22	D002	PB002	7
22-Dec-22	D002	PC003	5
1-Jan-22	E001	PA001	2
4-Mar-22	E001	PA001	8
7-Jun-22	E001	PA001	66
24-Sep-22	E001	PA001	34

While I'd like to insert the last column "next purchase date" with result like below

Date	Customer	Product	Qty	Next Date
5-Feb-22	B004	PC003	7	9-May-22
9-May-22	B004	PC003	12	25-Aug-22
25-Aug-22	B004	PC003	88	7-Nov-22
7-Nov-22	B004	PC003	23
8-Apr-22	D002	PB002	9	22-Jul-22
22-Jul-22	D002	PB002	46	21-Oct-22
21-Oct-22	D002	PB002	7
22-Dec-22	D002	PC003	5
1-Jan-22	E001	PA001	2	4-Mar-22
4-Mar-22	E001	PA001	8	7-Jun-22
7-Jun-22	E001	PA001	66	24-Sep-22
24-Sep-22	E001	PA001	34

I'm trying the "DATEADD" or "PARALLELPERIOD" but unable to get the result. Thanks a lot!!!

[elvincplee]

Thanks for your prompt help. How about if customer buying the same product on the same day? (there is tiny chance but can it work if indexed the column?)

[m_alireza]

Hi @elvincplee ,

What is the expected output if a customer buys the same product on the same day? should the earliest occurances of the purchase have the date in the Next Date even though its on the same day and the latest blank? Or should it all be blank?
e.g. 

One thing I didnt mention was the sort order for the date. In the example you gave, the dates were in the order I needed them to be for the index to work. But if the dates are all scrambled, then you need the index to sort by date in ascending order, grouped by customer and product. 

To do that, instead of adding the index column, add this step instead, changing the references as required. Make sure date column has date as data type before doing this.

= Table.Group(Source, {"Customer"}, {{"AllRows", (tableint) => let sort = Table.Sort(tableint,{"Date", Order.Ascending}), AddIndex = Table.AddIndexColumn(sort,"Index",1) in AddIndex}})

And then expand all rows. 

Attached is a sample pbix file so its easier to understand. You can modify it as needed to fit your purpose.

https://drive.google.com/file/d/1tmfjCeoGbaO2qsiAhq_pUKKL7J6j4Nlj/view?usp=sharing 

[m_alireza · Accepted solution]

Hi @elvincplee ,

First, create an index column. 
You can do this by opening Power Query Editor, go to Add Column tab and click on Index Column. After you do that, close Power Query Editor. 



Then create a calculated column as below: 

Next Date = 

CALCULATE(
    MAX('Table'[Date]),
    FILTER(
        'Table',
        'Table'[Index] = EARLIER('Table'[Index],1) +1 && 'Table'[Customer]=EARLIER('Table'[Customer]) && 'Table'[Product]=EARLIER('Table'[Product]
        )))

Sample output: