Dear Experts
I need to calculate the average frequency of days between orders for customer & Product, here is an example for the output of the Dax Caluclation for customer:
Also the same thing for Products (SKUs).
Thank you.
Also the same thing for Products (SKUs).
Hello @MAAbdullah47
Thank You for posting your query onto the Forum.
Well, it would be great if you could attach the working of the PBIX file so that members of our forum can provide the better results efficiently.
Best,
Kumail Raza
It would be better if you could share your pbix file. However, I am attaching a sample pbix file that suits in your case. The measure used for Average Days is
Avg Delivery =
AVERAGEX ( Sales, Sales[Delivery Date] - Sales[Order Date] )
Replace it with
Avg Days =
AVERAGEX ( Orders, Orders[Last Order Date] - Orders[First Order Date] )
Avg Days.pbix (2.0 MB)
Thanks.
I completely agree with @MudassirAli that you’ll always get the best response if you provide a PBIX and a mockup of the results you want to see. In this case, I also worked up a solution, but with a different understanding of your requirement. My analysis calculates the days between each order date for each customer, and then takes the average of that days between figure.
Generally, I don’t recommend the use of calculated columns if it can be avoided, but in this case I think it makes the solution simpler and easier to understand.
The first calculated column determines the previous order date for each record in the Sales table:
Prev Order Date =
VAR MaxIndex =
CALCULATE(
MAX( Sales[Index] ),
FILTER(
Sales,
Sales[Customer Name Index] = EARLIER( Sales[Customer Name Index] ) &&
Sales[OrderDate] < EARLIER( Sales[OrderDate] )
)
)
VAR Result =
CALCULATE(
MAX( Sales[OrderDate] ),
FILTER(
Sales,
Sales[Index] = MaxIndex
)
)
RETURN
Result
The second calculated column just calculates the difference between each order date and previous order date by customer:
Days Between =
DATEDIFF(
Sales[Prev Order Date],
Sales[OrderDate],
DAY
)
Finally, this measure takes the average of the column calculated above
Avg Days Between =
AVERAGE( Sales[Days Between] )
Here’s what it looks like all put together:
I hope this is helpful. Full solution file attached below.
- Brian
2 Likes
@BrianJ you can also use VAR to provide outer row context and can avoid using Earlier.
Prev Order Date =
VAR CustomerNameIndex = Sales[Customer Name Index]
VAR OrderDate = Sales[OrderDate]
VAR MaxIndex =
CALCULATE (
MAX ( Sales[Index] ),
FILTER (
Sales,
Sales[Customer Name Index] = CustomerNameIndex
&& Sales[OrderDate] < OrderDate
)
)
VAR Result =
CALCULATE (
MAX ( Sales[OrderDate] ),
FILTER ( Sales, Sales[Index] = MaxIndex )
)
RETURN
Result
1 Like
Yup. I was hurrying this morning, and got lazy but you are absolutely right about that being the better approach.
In the SQLBi Dax Guide, EARLIER now gets a big, red NOT RECOMMENDED…
- Brian
1 Like
I’ll check and get back to you.
Hi @BrianJ
I have question for the Measure :
Num Sales =
COUNTROWS( Sales )
The ordernum is duplicated for different Items (SKUs) for example:
Order order date Item Customer
1 1 Nov 2020 X 7
1 1 Nov 2020 Y 7
3 2 Nov 2020 Z 8
4 2 Nov 2020 Y 8
I think countrows not work with my case, Is distinctcount ok?
Yes, that should work fine. And I just threw the Num Sales measure in as a validation check - it’s not at all necessary for the Average Days Btw Orders calculation.
- Brian
@BrianJ The sales table is almost the same as your example but I don’t have the Column Index?
How I create it? If it is not possible what column I can use Instead of Index in Sales?
Oh, I should have mentioned that in my prior post. Just go into Power Query and do the following:
- Sort ascending on your customer id and then ascending on order date
- take the following steps to add an index column:
- Brian
1 Like
Hi @MAAbdullah47, we’ve noticed that no response has been received from you since the 10th of November. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
1 Like
I responded, I put Like on the solution, if there is a need for another action please advice.
Hi @MudassirAli
Please check this example:
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?
I have reworked the solution, eliminating the use of EARLIER by replacing it with a variable tracking the current row context. I have also converted all of the prior calculated columns to measures, and I believe the solution is now working properly per your requirements.
I’ve attached the revised solution file - please take a look and let me know if this meets your needs. Thanks.
- Brian
4 Likes
Thank you So much @BrianJ, I belive this solution is much accurate than the Pure Indexing , Many thanks.
Great – glad to hear that solution met your needs. I appreciated your input, and I do agree that for your use case, working directly with the dates ended up being a much better approach than using an intermediate index field. I also appreciated @MudassirAli’s comment that using variables was preferable to the use of EARLIER, and think that also improved the revised solution. Nice teamwork all around on this one.
- Brian
1 Like