# Chennai Power BI User Group

View Only

## Duration between Order dates for each Customer

• #### 1.  Duration between Order dates for each Customer

Posted Jan 21, 2020 04:28 AM
Hello There,

I am very new to Power BI and DAX functions.

I have a data set by name 'Billed_Orders' which is purely a sales order data containing columns like Customer Name, Order No, Order Date, Product Code, Product Name, Quantity, Value....

I am looking to calculate No. of days between each order for every customer and No. of days between orders for each product from every customer.

The ultimate goal is to get the average interval in days between orders and also the orders of specific products for each customer.

I tried to calculate the interval by inserting calculated columns like

Previous_Order_Date = CALCULATE(MAX(Billed_Orders[Order Date]),(FILTER(Billed_Orders,EARLIER(Billed_Orders[Customer Name])=Billed_Orders[Customer Name] && EARLIER(Billed_Orders[Order Date])>Billed_Orders[Order Date])))

Days_Btw_Orders = DATEDIFF(Billed_Orders[Previous_Order_Date],Billed_Orders[Order Date],Day)

But this is not giving me accurate results.

Note: There are a total 83 products a customer can order for and if a customer order for 6 products, the order date will repeat six times in the data table.

Sample Data table is as below

 Customer Code Customer Name Customer Group Order No Order Date Product Code Product Name Order Quantity Value 800002 Cust_A Modern Trade 6191 21-Sep-19 9000017 Prod_A 120 10000 800002 Cust_A Modern Trade 6191 21-Sep-19 9000018 Prod_B 220 15000 800002 Cust_A Modern Trade 6191 21-Sep-19 9000019 Prod_C 350 12000 800002 Cust_A Modern Trade 6191 21-Sep-19 9000020 Prod_D 90 33000 800002 Cust_A Modern Trade 6192 23-Sep-19 9000021 Prod_E 40 8000 800003 Cust_B Modern Trade 6193 23-Sep-19 9000017 Prod A 150 12500 800003 Cust_B Modern Trade 6193 23-Sep-19 9000019 Prod C 300 10286 800003 Cust_B Modern Trade 6194 05-Oct-19 9000017 Prod_A 120 10000 800002 Cust_A Modern Trade 6195 12-Oct-19 9000019 Prod C 300 10286

------------------------------
Rajesh TL

------------------------------