Calculating Order Frequency by Customer

[rutgerboll]

I am attempting to create a measure or series of measures that will allow me to do the following steps:

1. Calculate time between each order placed by a customer
2. Calculate the avg time between orders per customer
3. 3) Break the data into quintiles

*Must retain the context of slicers/filters on the page

Currently I am doing this via a SQL query that returns a summary table with the quintiles already calculated.

I would like to do this in Power BI so I can filter the query without using parameters.

My data set includes the following tables

1. FactLine (transaction lines)
2. DimDate
3. DimProduct (product details)
4. DimCustomer

These tables are related to FactLine. How can I calculate the average order frequency by customer then break it out into quintiles? Thanks in advance for all help.

[v-juanli-msft]

Hi @rutgerboll

Could you show the summary table with the quintiles already calculated for me to refer to?

As for some details, please confirm:

1.Calculate time between each order placed by a customer 

eg.

customer1     order   date

1                     1         2018/1/1

1                     2         2018/1/7

for customer1, time between each order =2018/1/7-2018/1/1=6 days, right?

2.Calculate the avg time between orders per customer

for customer1, avg time between orders=6days/2 orders=3, right?

3.Break the data into quintiles

???

Best Regards

Maggie