Dynamic Average Days Between Orders Based on the selcected calendar filter

[MAAbdullah_47]

Dear Experts

I need to calculate The Averge days between orders for particuler customer (all) under the following criteria:
-The frequency of days should be dynamic to change based on the calendar period I select (filter), Here is an example:
Customer (Peter) Purchase goods from store during the period from 1 April to 20 April 2020 Around 6 times in that month.
Scienario - A:
I want to calculate the Average frequency of Times (Days) Between orders of Customer name Peter , during Month of April 2020 :
The result will be:
Customer Peter Purchase from 1 April to 20 April 2020 Every 3 days= 6.66 Times frequency .

Scinario-B:
I want to check the Average frequency of Times (Days) Between orders of Customer name Peter , during the period 1 January 2020 Upto Month of September 2020 :
The result will be:
Every 45 days= (n) Times frequency .

The same thing for all other customers in (Table custom visual), I want to calculate the Average days (times) between orders based on the Filter of Calender I select.
Attached sample Pbix file.https://drive.google.com/file/d/1_3V0TQXvBTCjSAw1jmZR-eyU3T2F9MP6/view?usp=sharing 

[AlB]

@MAAbdullah_47 

I don't undestand your last message

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[MAAbdullah_47]

Completely not working Please check the below-screen shot it gives one value for all customers and when I change slicer it gives another number but the same for all customers, I have date table connected to Sales table the same structure of your file you send it before.

[MAAbdullah_47]

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?

[Anonymous]

// Here's the solution according to my understanding.
// There are at least 3 tables in the model:
// A Dates table that is marked as a date table,
// Orders and Customers. There is a 1-to-many between
// Dates[Date] and Orders[OrderDate] (hidden column
// from which you CANNOT select) and betwen Customers[CustomerID]
// and Orders[CustomerID] (hidden column). Slicing is always
// done via dimensions. Let's assume that Orders[OrderId]
// is an integer and the higher the OrderId, the
// later the order. This, of course, can be adjusted
// accordingly. For instance, if OrderId is not in
// chronological order or it's not an integer, then
// we'll have to use the OrderDate for sorting.

[Avg Time Between Orders] =
// First, we have to find
// all orders within the period
// (leaving out the last one)
// and calculate the number
// of days from one order to the
// next one. This will give good
// results as long as there is only
// one customer visible because then
// multiple orders can't be placed
// at the same time. This is not true
// when there is a mixture of orders
// from different customers. If you
// want to be able to calculate this
// for many customers, then you have to
// define what it means "the average time
// between orders."
if( HASONEVALUE( Customers[CustomerId] ),
    AVERAGEX(
        DISTINCT( Orders[OrderID] ),
        // Find the next order and
        // take the diff in order dates
        var __currentOrder = Orders[OrderID]
        var __currentOrderDate = 
            CALCULATE(
                // This will return only one value
                // since there's only one order visible
                // and all orders do have an order date.
                SELECTEDVALUE( Orders[OrderDate] )
            )
        var __nextOrderDate =
            MIN(
                filter(
                    Orders,
                    Orders[OrderID] > __currentOrder
                ),
                Orders[OrderDate]
            )
        var __diff =
            // Please note that if __nextOrderDate
            // is BLANK, meaning the next order
            // is outside the period selected,
            // this function returns BLANK and
            // as such the value is not taken into
            // account when calculating the average.
            // This is precisely what we want -
            // the last order in the period must
            // not be taken into account.
            DATEDIFF( 
                __currentOrderDate,
                __nextOrderDate,
                DAY
            )
        return
            __diff
    )
)

[MAAbdullah_47]

Hi @Anonymous 

Please check the Order[Orderdate] it gives error.

[MAAbdullah_47]

Hi @Anonymous  

It doesn't except the (2) colums?!!

[Anonymous]

@MAAbdullah_47, you have to adjust the code to your model. The error tells you that you don't have fields in your table(s) with the names I used. Just use the right names from your model.

[MAAbdullah_47]

Thank you so much I'll test and get back to you.

[MAAbdullah_47]

One More Question If I want the frequency of oder by time (e.g. 2.5 Times per year) Shall do:

Time Frequency = 365/[average days between orders] ? 

[daxer-almighty]

Hi @MAAbdullah_47 

I think your formula is almost correct for the calculation of the yearly frequency based on the freqency in period T. You just have to use the number of days in the period you're considering instead of 365. So, the formula would be:

[Yearly Order Freq] =
    DIVIDE(
        COUNTROWS( 'Dates' ),
        [Avg Mean Time Between Orders]
    )

What you're doing here is you're extrapolating to the whole year.

If you want to calculate the mean time between orders for the whole year based on the selected period of time T, you'll need this formula:

[Yearly Mean Time Between Orders] =
    [Avg Time Between Orders]
    * DIVIDE(
        365.25, 
        COUNTROWS( 'Dates' )
    )

Why such a formula? Well, because you want this equation to hold true (you want the frequency in period T to be equal to the yearly freqency):

    T            365.25

------- = -----------

  A(T)         A(365.25)

where A(T) is the average time beteen orders in the period T and A(365.25) is the average in the year.

If you solve for A(365.25), you'll get the above.

[MAAbdullah_47]

Hi @daxer-almighty  Again

If I understand your solution correctly No Of rows = 4 , and Avg Time = 134.5 , If you divide 4/134.5=0.029

But the Result = 2 how this happened? 

[Anonymous]

You're calculating this not from the count of rows in orders [Order Date] but from the number of days you've selected in your calendar. Just please read my formulas correctly and everything will be working well.

[MAAbdullah_47]

Hi @daxer-almighty  I get confused here : 

Can you give me the formula of [Average Mean Time Between Orders] do you means the measure posted by @AlB ?

[Anonymous]

No, his formula seems to be wrong; he's calculating something entirely different than what you want. I have given you the code: it's [Avg Time Between Orders] above.

[MAAbdullah_47]

Can You Explain More @Anonymous  still confused, Kindly could you put the text of the right formula?

[Anonymous]

@AlB 

I'm not entirely sure your calculation (and even the description by @MAAbdullah_47) is correct. If we want to know the average number of days between orders, then we can't just take the number of days in the selected period and divide it by the number of orders. To me this seems wrong. What needs to be done is more complex. Here's what needs to be done. We have to start with the first order in the selected period and find the next one in the period. Then take the difference in days. Next, take the second order and find the next one. Find the difference in days between them. Repeat this for all orders in the selected period as long as the next order is in the same period. This also means the last order in the period must NOT be considered (since the next order will not be in the selected period). Once we have all the differences, we have to return their average. This is, to my mind, the average number of days between orders and it will definitely be different (easily demonstrable) than just taking the number of days in the period and dividing be the number of orders.

[AlB]

@MAAbdullah_47 

It would be good to create a date table and operate with it instead of with the dates on your fact table. You can use it for the slicer to select the period. You'd also need a relationship between the date table and the Orders table (through Order date)

1. Place Customer ID (and Customer Name) in a table visual

2. Create this measure and place it in the visual

Measure =
VAR numDaysInPeriod = COUNTROWS ( DISTINCT ( DateT[Date] ) )
VAR numOrders_ = DISTINCTCOUNT ( Orders[Order ID] )
RETURN
    DIVIDE ( numDaysInPeriod, numOrders_ )

Note that it divides the number of days in the selected period by the number of orders for that client in the period. That would be the number of days between orders as you seem to describe/want it. Change it if necessary.  It considers one order = one orderID and it takes the order date as as date of purchase

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[MAAbdullah_47]

This not work in other pbix files.

[MAAbdullah_47]

Hi @AlB  Yes In My real file I did this( creating date table).

[MAAbdullah_47]

Hi @AlB 

Your solution is works fine on the file you Attached it in the previous post, I tried to implemint it exactly on another Pbix File It didn't seems works well , give me a chance to test and get back to you if needed.