Average Days Between Orders

DAX DAX Calculations
1

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.
Customers Dynamic Segmentation by Order Count.pbix (649.8 KB)

2

@MAAbdullah47

Thanks for posting the question on the forum. Is your requirement different than the following?

I believe if you take the mentioned steps in the post you will get the desired result.

Thanks.

3

Hi @MudassirAli
No I tried it , It gives the same results if I select any calender period there is no change (Same results regardless filtering calendar).
You can try .

4

@MAAbdullah47
The previous solution seems to be working

1
11920×1080 162 KB

2
21920×1080 180 KB

5

@MAAbdullah47 Data in your PBI files ends on 12/30/2017 :frowning:

1 Like
6

@AntrikshSharma spot on :+1:

1 Like
7

Actually I give this as an example not from my data.

8

I’ll check.

9

Hi @MAAbdullah47, did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

10

I have small problem @MudassirAli , I want to create Index through power query but The data I have is too big (2.5M) rows, Always crash out of memory, do you have an Idea how to create the Index through (DAX) based on date Instead of Power Query?

11

@AntrikshSharma @MudassirAli I meant create a column not measure.

12

@MAAbdullah47 If PQ can’t do it, I am sure DAX can’t handle it as well, you will have to do it at the data source. But why add index to 2.5 M rows?

13

Hi @AntrikshSharma
because the solution require this , please re-check it.

14

If I do it from the Source file, It is csv when you open it through excel it will shrinking
did you got it?

15

@MAAbdullah47 Doing indexing is not a problem outside PBI, I can do it with VBA and Python, but the point you should ask yourself is if you really need indexing? it will increase the size of your model. What is the goal that you want to achieve with index?

16

I want to solve the problem of Days Between Orders to be dynamic, you posted solution before but it doesn’t work dynamically, @MudassirAli post one works fine with small db (Creating index) but with the cussrent db it doesn’t work , did you got it @AntrikshSharma?

17

Hi Experts again

Please check the example below:

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?

18

@MAAbdullah47 The current and this post

are these same?

19

Hi @AntrikshSharma

Yes But now I encountered wrong results.

20

@AntrikshSharma, @MAAbdullah47,

I think we should merge these two threads together, since they seem to be addressing basically the same question.

Re: my earlier solution, we can rework that without the index. I use it because I think it adds clarity to the solution, but it’s not necessary.

@AntrikshSharma - how do you want to proceed?

  • Brian