Question

How do I calculate the average time between two events for each customer?

i  need a Power BI measure that calculates the average time between two specific events for each customer, such as the time between when an order is placed and when it is delivered. The calculation should be dynamic, adjusting based on filters like time periods or customer groups. How can I use DAX functions like DATEDIFF or AVERAGEX to compute the average time and ensure it adjusts with user filters?

Answer 1

To calculate the average time between two specific events for each customer in Power BI, you can use DAX functions like DATEDIFF, AVERAGEX, and CALCULATE to compute the time difference dynamically. Below is the approach to achieve this:

Step-by-step process:

1. Define the Two Events: You'll need to have columns that represent the timestamps for the two events (e.g., "Order Date" and "Delivery Date") in your dataset.

2. Use DAX Functions:

   • DATEDIFF: Calculates the difference between two dates in a specified time unit (e.g., days, hours).

   • AVERAGEX: Calculates the average of an expression evaluated over a table, which allows you to iterate over each customer.

Example DAX Measure:

​Average Time Between Events = 
AVERAGEX(
    VALUES(Orders[CustomerID]), 
    DATEDIFF(
        FIRSTNONBLANK(Orders[OrderDate], Orders[OrderDate]), 
        FIRSTNONBLANK(Orders[DeliveryDate], Orders[DeliveryDate]), 
        DAY
    )
)

Explanation:

• VALUES(Orders[CustomerID]): This returns a unique list of customer IDs, so the AVERAGEX function calculates the measure for each customer.

• FIRSTNONBLANK(Orders[OrderDate], Orders[OrderDate]): This retrieves the first non-blank order date for each customer.

• FIRSTNONBLANK(Orders[DeliveryDate], Orders[DeliveryDate]): This retrieves the first non-blank delivery date for each customer.

• DATEDIFF: This calculates the difference between the order date and the delivery date in days (you can change DAY to HOUR, MINUTE, etc., depending on the time unit you want).

• AVERAGEX: This calculates the average time across all customers in the filtered context, which adjusts dynamically based on applied filters like customer groups or time periods.

Result:

• This measure will return the average time between the two events (order and delivery) for each customer, adjusting based on any filters applied (e.g., by customer, date range, etc.).

• The result will be dynamic and update according to the filters or slicers selected by the user.