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

One thought on “DAX Tutorial: Average Days Between Orders

  1. when I want to use the first code, I always get a message at the var because he expects measures there but I would say that I should put the customer ID and date hard in there from my table but this is not caught

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 )

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