DAX Tutorial: Average Days Between Orders

How often does our customer place an order with us? This is a common business scenario, therefore, I decided to cover it in another DAX Tutorial. There are two ways this question can be posed:

1. How many days on average does it take the customer to place another order?
2. How often does the customer order the same product?

As you can see, there are some slight variations in those two questions, so I will cover both of them in my video.

You can download the Power BI Model here.

 

These are the calculations used in the video:

Days Since Last Order = 
var currentCustomer = Sales[CustomerID]
var currentDate = Sales[Date]
var lastOrderDate = 
CALCULATE(
    MAX('Date'[Date]), 
    FILTER(
        ALL(Sales), 
        Sales[CustomerID] = currentCustomer && 
        Sales[Date]<currentDate
    )
)

return IF(lastOrderDate=BLANK(), BLANK(), currentDate - lastOrderDate)

Days Since Last Order (Same Product) = 
var currentCustomer = Sales[CustomerID]
var currentDate = Sales[Date]
var currentProduct = Sales[ProductID]
var lastOrderDate = 
CALCULATE(
    MAX('Date'[Date]), 
    FILTER(
        ALL(Sales), 
        Sales[CustomerID] = currentCustomer && 
        Sales[ProductID] = currentProduct &&
        Sales[Date] < currentDate
    )
)

return IF(lastOrderDate=BLANK(), BLANK(), currentDate - lastOrderDate)

Average Days Between Orders = 
AVERAGEX(
    ADDCOLUMNS(
        VALUES(Customer[CustomerID]),
        "Average Days Between Orders by Customer", 
        AVERAGE(Sales[Days Since Last Order])
    ),
    [Average Days Between Orders by Customer]
)