Days Between 1st or Nth Orders for different customers

Dear Experts
I need to know the Average Number of days Between Order (n) and Order (n+1) for all customers not for Particuler Customer here is an example:

Assuming we have (175) customers they have a range of orders requested BTW (1 - n) orders, we need only Up to (10) Orders Maximum to calculate the average No of Days BTW (1-10) as the following screen-shot:

I attached PBix Sample with this thread.
DNA Forum – Average Days Between Solution2.pbix (445.7 KB)

Hi @MAAbdullah47, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

    image

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

Please Just look at the editing I did , In Addition I’m sure the Topic not Submitted before in this forum.

Hello @MAAbdullah47
I checked the PBIX file you attached above, and you are doing great job, I couldn’t solve your problem, but I think these modifications may help you to step forward the solution

the modifications are

  • get the next order date per customer,
  • calculate the number of days to next order,
  • add an index for the orders
    enda - days between post 01

here are the formulas

[Next Order Date] =
        VAR CurrentSalesDate = SELECTEDVALUE( Sales[OrderDate] )
        VAR NextSalesDate =
            CALCULATE(
                MIN( Sales[OrderDate] ),
                FILTER( ALL( Sales[OrderDate] ), Sales[OrderDate] > CurrentSalesDate )
            )
        RETURN
            NextSalesDate

[Days to Next Order] =
DATEDIFF( SELECTEDVALUE( Sales[OrderDate] ), [Next Order Date], DAY )

var customer_dates = SUMMARIZE( Sales, Sales[Customer Name Index], Sales[OrderDate] )
VAR vTable =
ADDCOLUMNS(
customer_dates ,
@NextDate”, [Next Order Date],
@DaysBtw”, [Days to Next Order],
“order_rnk”,
var current_customer = Sales[Customer Name Index]
RETURN
CALCULATE( RANKX( filter( customer_dates, Sales[Customer Name Index] = current_customer) , CALCULATE(min(Sales[OrderDate])), ASC ) )
–“@PrevDate”, [Prev Order Date],
–“@DaysBtw”, [Days Between]
)
VAR vTable_limited =
–FILTER( vtable, Sales[Customer Name Index] = 1 || Sales[Customer Name Index] = 3)
FILTER( vtable,
[order_rnk] <= 10 && [@NextDate] <> BLANK()
)
VAR Result =
AVERAGEX( vTable, [@DaysBtw] )
RETURN
vTable_limited

I think the next step is building a disconnected table (1,2,3,…,10) to be used in the table, then build a new calculation to link “vTable_limited” with this disconnected table.

Thank you @Moatasem I’ll check the solution and get back to you.

Ho To Add Index to the orders?

I am not sure what do you mean?

the orders are indexed per customer, using the order_rnk field inside ADDCOLUMNS function.

Hi @Moatasem
I attached the pbix file , there is an error in the last measure (var customer_dates ) , could you check it please.DNA Forum – Average Days Between Solution3.pbix (446.0 KB)

Hi @MAAbdullah47
The formula was returning a table not a scalar value, which caused this problem, to fix this issue, I wrapped the variable “vTable_limited” with COUNTROWS function.

As I mentioned before, this formula doesn’t solve the problem, but you can use it as a guidance.

DNA Forum – Average Days Between Solution3.pbix (446.3 KB)

Hi @MAAbdullah47 , did the response provided by @Moatasem 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!*

Hi @Moatasem For Creating the Disconnected table No problem it is easy but the Table “vTable_Limit” Still didn’t generated , how can we generate it?
Could you help me in Showing how to generating it from the measure? In addition how to count the No of customers For the sequence of the orders? e.g.: From 1 to 2 (1000 Customer), from 2 to 3 (800 Customer) ,…etc. I attached what I did in the file.
DNA Forum – Average Days Between Solution4.pbix (449.5 KB)

Hello @MAAbdullah47, due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread. When starting a new post, I suggest linking this thread, provide as much context to a question as possible and include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details. Thanks!

Hi @EnterpriseDNA

Still Not reciving any update from @Moatasem , I thank for him but still not been solved if you wish to close it close it but it didn’t been solved.