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]
)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s