Calculating the Average frequency of Days Between Orders

Dear Experts
I need to calculate the average frequency of days between orders for customer & Product, here is an example for the output of the Dax Caluclation for customer:


Also the same thing for Products (SKUs).

Thank you.

Hello @MAAbdullah47

Thank You for posting your query onto the Forum.

Well, it would be great if you could attach the working of the PBIX file so that members of our forum can provide the better results efficiently.

Best,
Kumail Raza

@MAAbdullah47

It would be better if you could share your pbix file. However, I am attaching a sample pbix file that suits in your case. The measure used for Average Days is

Avg Delivery =
AVERAGEX ( Sales, Sales[Delivery Date] - Sales[Order Date] )

Replace it with

Avg Days =
AVERAGEX ( Orders, Orders[Last Order Date] - Orders[First Order Date] )

Avg Days.pbix (2.0 MB)

Thanks.

@MAAbdullah47,

I completely agree with @MudassirAli that you’ll always get the best response if you provide a PBIX and a mockup of the results you want to see. In this case, I also worked up a solution, but with a different understanding of your requirement. My analysis calculates the days between each order date for each customer, and then takes the average of that days between figure.

Generally, I don’t recommend the use of calculated columns if it can be avoided, but in this case I think it makes the solution simpler and easier to understand.

The first calculated column determines the previous order date for each record in the Sales table:

Prev Order Date = 

VAR MaxIndex =
CALCULATE(
    MAX( Sales[Index] ),
    FILTER(
        Sales,
        Sales[Customer Name Index] = EARLIER( Sales[Customer Name Index] ) &&
        Sales[OrderDate] < EARLIER(  Sales[OrderDate] )
    )
)

VAR Result =
CALCULATE(
    MAX( Sales[OrderDate] ),
    FILTER(
        Sales,
        Sales[Index] = MaxIndex
    )
)

RETURN
Result 

The second calculated column just calculates the difference between each order date and previous order date by customer:
Days Between =

DATEDIFF(
    Sales[Prev Order Date],
    Sales[OrderDate],
    DAY
)

Finally, this measure takes the average of the column calculated above

Avg Days Between = 
AVERAGE( Sales[Days Between] )

Here’s what it looks like all put together:

image

I hope this is helpful. Full solution file attached below.

  • Brian

DNA Forum – Average Days Between Solution.pbix (468.4 KB)

2 Likes

@BrianJ you can also use VAR to provide outer row context and can avoid using Earlier.

Prev Order Date =
VAR CustomerNameIndex = Sales[Customer Name Index]
VAR OrderDate = Sales[OrderDate]
VAR MaxIndex =
    CALCULATE (
        MAX ( Sales[Index] ),
        FILTER (
            Sales,
            Sales[Customer Name Index] = CustomerNameIndex
                && Sales[OrderDate] < OrderDate
        )
    )
                   VAR Result =
                    CALCULATE (
                      MAX ( Sales[OrderDate] ),
                        FILTER ( Sales, Sales[Index] = MaxIndex )
    )

RETURN
Result

1 Like

@MudassirAli,

Yup. I was hurrying this morning, and got lazy but you are absolutely right about that being the better approach.

In the SQLBi Dax Guide, EARLIER now gets a big, red NOT RECOMMENDED…

  • Brian
1 Like

I’ll check and get back to you.

Hi @BrianJ

I have question for the Measure :
Num Sales =
COUNTROWS( Sales )

The ordernum is duplicated for different Items (SKUs) for example:

Order order date Item Customer
1 1 Nov 2020 X 7
1 1 Nov 2020 Y 7
3 2 Nov 2020 Z 8
4 2 Nov 2020 Y 8

I think countrows not work with my case, Is distinctcount ok?

@MAAbdullah47,

Yes, that should work fine. And I just threw the Num Sales measure in as a validation check - it’s not at all necessary for the Average Days Btw Orders calculation.

  • Brian

@BrianJ The sales table is almost the same as your example but I don’t have the Column Index?
How I create it? If it is not possible what column I can use Instead of Index in Sales?

@MAAbdullah47,

Oh, I should have mentioned that in my prior post. Just go into Power Query and do the following:

  1. Sort ascending on your customer id and then ascending on order date
  2. take the following steps to add an index column:

image

  • Brian
1 Like

Hi @MAAbdullah47, we’ve noticed that no response has been received from you since the 10th of November. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

1 Like

I responded, I put Like on the solution, if there is a need for another action please advice.

@MAAbdullah47,

Thanks – good to go. I’ve closed this out.

  • Brian

Hi @MudassirAli

Please check this example:
Hi @AlB

Customer X

I need to calculate the difference between each order (n) and Order (n+1) how many days?

then from Order (n+1) to Order (n+2) How many days?

…etc

Customer X+1

I need to calculate the difference between each order (n) and Order (n+1) how many days?

then from Order (n+1) to Order (n+2) How many days?

…etc

Then I need the (Average Days) Between Orders for all customers is the question clear?

Some Of the calculation work ok and some not working please check the below screen-shot:

Hi @BrianJ
Please check my comment below.