Power BI DAX: DATEADD using calculated date and interval

Question

I'm hoping someone could help me with my problem:

The first table is my Data and the second table is the summary that I'm trying to achieve using DAX Measure in Power BI. I was able to do all except for the last column; Next Expected Order Date.

So basically, I'm trying to calculate the customers next expected order date based on the history of their orders by calculating the number of days between their first and last orders and dividing it by the number of orders they had. In this case, they are more likely to order every 8 days in average. Therefore, the next expected order is 14/10/2023.

Here's what I've tried so far but both doesn't work:

Order Next Order Date = DATEADD(MAX('Sales Orders'[Order Date]), [Order_Average Frequency (Days)], DAY)

Order Next Order Date = MAX(DATEADD('Sales Orders'[Order Date], [Order_Average Frequency (Days)], DAY))

Does anyone have idea how to achieve this? Any idea/suggestions is highly appreciated.

Thanks!

Answer 1

Try this. Ideally, you should add a date table and proper star schema.

Measure = 
VAR x= MIN('Table'[Order Date])
VAR y = MAX('Table'[Order Date])
VAR days = DATEDIFF(x,y,DAY)
RETURN y+ days/COUNTROWS('Table')