topic Re: Dynamic Average Days Between Orders Based on the selcected calendar filter in DAX Commands and Tips

Dynamic Average Days Between Orders Based on the selcected calendar filter

MAAbdullah_47 — Tue, 17 Nov 2020 16:49:21 GMT

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

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

AlB — Tue, 17 Nov 2020 20:23:44 GMT

Hi MAAbdullah_47

The link provided requires to sig in with a google account. Can you either remove that restriction or upload the file somewhere else (Dropbox, OneDrive, tinyupload.com, etc.)?

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

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

MAAbdullah_47 — Wed, 18 Nov 2020 03:24:07 GMT

hi AlB

Sorry about this mistake, please here is the link without restrections:

https://drive.google.com/file/d/1_3V0TQXvBTCjSAw1jmZR-eyU3T2F9MP6/view?usp=sharing

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

AlB — Wed, 18 Nov 2020 14:46:31 GMT

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

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

Anonymous — Wed, 18 Nov 2020 14:23:36 GMT

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.

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

Anonymous — Wed, 18 Nov 2020 14:52:27 GMT

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

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

MAAbdullah_47 — Wed, 18 Nov 2020 15:23:25 GMT

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

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

MAAbdullah_47 — Wed, 18 Nov 2020 15:26:34 GMT

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

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

daxer-almighty — Wed, 18 Nov 2020 23:21:23 GMT

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.

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

MAAbdullah_47 — Thu, 19 Nov 2020 08:03:25 GMT

Hi Anonymous

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

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

MAAbdullah_47 — Thu, 19 Nov 2020 08:24:35 GMT

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 ?

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

MAAbdullah_47 — Thu, 19 Nov 2020 09:13:45 GMT

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?

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

MAAbdullah_47 — Thu, 19 Nov 2020 09:21:47 GMT

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.

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

Anonymous — Thu, 19 Nov 2020 15:28:45 GMT

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.

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

Anonymous — Thu, 19 Nov 2020 15:34:04 GMT

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.

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

Anonymous — Thu, 19 Nov 2020 15:35:45 GMT

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.

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

MAAbdullah_47 — Thu, 19 Nov 2020 15:38:33 GMT

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

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

MAAbdullah_47 — Tue, 24 Nov 2020 06:28:51 GMT

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

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

AlB — Tue, 24 Nov 2020 08:21:40 GMT

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

Re: Dynamic Average Days Between Orders Based on the selcected calendar filter

MAAbdullah_47 — Tue, 24 Nov 2020 09:29:35 GMT

This not work in other pbix files.