topic Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers in DAX Commands and Tips

Knowing The Number Of Days Between 1st or Nth Orders for different customers

MAAbdullah_47 — Tue, 01 Dec 2020 11:56:50 GMT

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:
From Ordder No To Order No No Of Customers Average No Of Days BTW Orders
1 2 100 22.3
2 3 70 33.3
3 4 65 10.7
,

9 10 40 15.3

I attached PBix Sample with this thread.

https://drive.google.com/file/d/1PRJZRipQ5n_RYGUwFRe1zMpZqri-7wdV/view?usp=sharing

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

Anonymous — Wed, 02 Dec 2020 17:40:25 GMT

// Assuming that there's: // 1. a Customers dimension, // 3. a Dates dimension (for the order dates), // 4. an Orders fact table. // The Orders fact (*-side) joins to Dates[Date] // dimension (1-side) on Orders[OrderDate] and to // Customers[CustomerID] on Orders[CustomerID]. // Orders must also have a column [Order No] // which will number orders (within each customer) // from 1 to N. All the ID columns in the fact table // and dimensions will, of course, be hidden. // The very last N available for a customer will, // of course, be different among customers as one // can't reasonably expect that all customers have // placed the exact number of orders. Once this // setup is in place, you can write a measure that will // return the average number of days between [Order No] = n // [Order No] = n+1 (all within the current context!). [Avg No of Days To Next Order] = // First, need to make sure that the column // Orders[Order No] is filtered. If there's // a filter on [Order No] = 1, then we'll be // calculating the average num of days between // the order numbers 1 and 2. If there's a filter // on order number 10, the measure will return // the avg number of days between order number // 10 and 11 and so on... if( hasonefilter( Orders[Order No] ), var __orderNumber = selectedvalue( Orders[Order No] ) var __avg = averagex( Orders, // For the current order find the next one // (within the same customer AND THE CURRENT // CONTEXT!) and caclulate the difference in // the order dates. var __currentOrderDate = Orders[OrderDate] return calculate( // Get the next order for the same // customer if there exists such an // order. If not, BLANK will be returned // and hence such a customer will not // participate in the calculation of // the average, as required. Please note // that this calculation fully respects // the current context, so based on the // filters there might not be an (n+1)th // order for the customer, even though it // will exist in a modified (broader) context. var __nextOrderDate = CALCULATE( SELECTEDVALUE( Orders[OrderDate], BLANK() ), Orders[Order No] = __orderNumber + 1 ) return DATEDIFF( __currentOrderDate, __nextOrderDate, DAY ) ) ) ) return __avg )

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

MAAbdullah_47 — Wed, 02 Dec 2020 18:21:06 GMT

Hi Anonymous thank you for your post , let me test it and get back to you.

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

MAAbdullah_47 — Wed, 02 Dec 2020 18:24:37 GMT

I have question Anonymous you wrote ("On order 10 measure will return")

which line on the measure mentions this?

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

MAAbdullah_47 — Wed, 02 Dec 2020 18:26:52 GMT

How we display the # customers on each of the 10 rows?

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

MAAbdullah_47 — Thu, 03 Dec 2020 10:12:58 GMT

Hi Anonymous

It gives the error below

The order number is Text not Number.

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

MAAbdullah_47 — Thu, 03 Dec 2020 10:22:41 GMT

Hi Anonymous

I think you consider the Order Number as Numerice , Please check the pbix file attached and assest me in impleminting it on the attached pbix file.

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

Anonymous — Thu, 03 Dec 2020 16:05:07 GMT

From the code it's obvious that if the code does something like a - 1, then it must be numeric or a date, depending on the context, since you can't mix data types in DAX. It's obvious as well that Order No must be numeric. Your initial post shows them as numeric, too. To see the number of customers the average is calculated against you have to create a separate measure taking mine as a template. I can't view the file you placed a link to because my company does not allow this. Hence I gave you a description of a correct model in the header of my measure.

"I have question @daxer you wrote ("On order 10 measure will return")

which line on the measure mentions this?"

The measure works on ANY selection of Order No. Your question is not comprehensible to me. Sorry.

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

MAAbdullah_47 — Thu, 03 Dec 2020 16:18:06 GMT

Hi Anonymous

I thought that you checked the attached file, the (1,2,.....etc) not means the order number (In the order table) it means (The 1st order of the customer , the 2nd order of the customer,.....etc, 10th order for the customer) , did you know what I means?

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

Anonymous — Thu, 03 Dec 2020 16:34:23 GMT

Mate, this is exactly what it means in my code 😡 Please read this once again with a focus. Thanks.

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

MAAbdullah_47 — Sun, 06 Dec 2020 06:59:16 GMT

Hi Anonymous again, can you show me at least how to call the measure above?

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

MAAbdullah_47 — Tue, 08 Dec 2020 12:29:46 GMT

Hi Member123456

Still I didn't get the solution , let me know what do you think , Mr. Anonymous appreciating his initiative for helping but he not welling in adding further assitance , I tried his solution it is not working because the order number is string , he told me you have to call the measure , I ask him how to call the measure but he never respond.
Thank you.

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

Fowmy — Wed, 09 Dec 2020 09:15:09 GMT

MAAbdullah_47

Can you provide an example based on the actual data that you have in your file with the expected result?

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube LinkedIn

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

MAAbdullah_47 — Wed, 09 Dec 2020 09:22:30 GMT

Thank you Fowmy

Here is the expected output:

Please note the Order No means the series of the order here is an example:

Order Number Series
----------------- ----
A 1

B 2

D 3

N 4

Thank you

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

Fowmy — Wed, 09 Dec 2020 09:58:51 GMT

MAAbdullah_47

Please read my comments. You need to provide an example using the data that is in the attachment you shared.

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube LinkedIn

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

MAAbdullah_47 — Wed, 09 Dec 2020 12:05:17 GMT

Thank you Fowmy I need that out put, I don't have it in the (pbix) file, I need measures to achive this

Please back to the earliest discussion with Mr. Anonymous he send me a measure to do this but I need to call this measure that calculate the average days BTW 2 Orders but the problem I don't know how to call this measure , I did post asking for your help to achive the results.

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

MAAbdullah_47 — Wed, 09 Dec 2020 12:11:29 GMT

Hi Fowmy

Please check the measure below posted by Anonymous , the problem I don't know how to call it, did you get the point?

[Avg No of Days To Next Order] =
// First, need to make sure that the column
// Orders[Order No] is filtered. If there's
// a filter on [Order No] = 1, then we'll be
// calculating the average num of days between
// the order numbers 1 and 2. If there's a filter
// on order number 10, the measure will return
// the avg number of days between order number
// 10 and 11 and so on...
if( hasonefilter( Orders[Order No] ),

var __orderNumber = selectedvalue( Orders[Order No] )
var __avg =
averagex(
Orders,
// For the current order find the next one
// (within the same customer AND THE CURRENT
// CONTEXT!) and caclulate the difference in
// the order dates.
var __currentOrderDate = Orders[OrderDate]
return
calculate(
// Get the next order for the same
// customer if there exists such an
// order. If not, BLANK will be returned
// and hence such a customer will not
// participate in the calculation of
// the average, as required. Please note
// that this calculation fully respects
// the current context, so based on the
// filters there might not be an (n+1)th
// order for the customer, even though it
// will exist in a modified (broader) context.
var __nextOrderDate =
CALCULATE(
SELECTEDVALUE( Orders[OrderDate], BLANK() ),
Orders[Order No] = __orderNumber + 1
)
return
DATEDIFF(
__currentOrderDate,
__nextOrderDate,
DAY
)
)
)
)
return
__avg

)

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

daxer-almighty — Wed, 09 Dec 2020 14:08:29 GMT

Here's a link to a PBI report that implements a solution. You should very carefully examine the file and the formulas to understand how it all works. Be especially careful to understand relative and absolute Order Sequence Numbers.

https://1drv.ms/u/s!ApyQEauTSLtOgY9D2FS2kAShjQvcww?e=lV4Vee

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

MAAbdullah_47 — Wed, 09 Dec 2020 16:08:32 GMT

Hi daxer-almighty I'm appreciating your help let me examin the solution carefully and get back to you as soon as I understand the complete picture , thank you my friend.

Re: Knowing The Number Of Days Between 1st or Nth Orders for different customers

MAAbdullah_47 — Wed, 09 Dec 2020 16:14:43 GMT

Hi daxer-almighty One question How you bring the (AbsoluteOrderSeqNo) to Orders I mean this column is it calculated colomn or what is it exactly ?