Chennai Power BI User Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.

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

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