Forum Discussion
DAX for next purchase date
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!!!
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:
3 Replies
- m_alireza
Solution Specialist
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: 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
