Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Calculate Days between OrderDate per Customer where Order date is in the same Column???

I'll start with Please Help!!

After Hours of trialing theories i need some help,

 

I have a data set which is purely sales data. containing Customer ID, CustomerName, OrderDate, Items Ordered etc...

 

What i am trying to do is calculate the difference in days per order per customer,

Then have a further measure which shows the Average days per order, per customer once i have the days between previous order.

 

I have created a Today Column and a Days Since Ordering Column to give me a calculation of Days Since Last Order.

 

What i want to do for the end result is,

 

Have Days Since Last Order - "Average Days Per Order" (Which i cant create) to give me a Numeric Value of How long on average it will be before the customer orders again...

 

The end result is so we can proactively sell to customers, so when the difference between Average days per order and days since last order is at 1 we can call the customer and say hey,

 

Any help will be appreciated beyond belief,

 

Thanks,

 

Josh

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Sorry Matt,

     

    Got it working not sure if it was me having the .Date on the date column and i selected CustomerID for the Values bit, but got rid of the .Date and ID so its like the columns below and done manual calculations to verify the data its outputing and its working now

     

    So this is what i ended up with;

     

    First Order = CALCULATE(FIRSTDATE(TableName[Date]),VALUES(Tablename[Date]))

    Last Order = CALCULATE(LASTDATE(TableName[Date]),VALUES(Tablename[Date]))

    Number of Orders = DISTINCTCOUNT(TableName[OrderID])

    First to Last Date period = ((TableName[Last Order] - TableName[First Order])*1)

    Today = Today()

    Days Since Last Order = TableName[Today].[Date] - TableName[Order Date].[Date] * 1

     

    Avg Order Period = divide(TableName[First to Last Date period],TableName[Number of Orders])

    &

    Difference = CALCULATE(MIN([Days Since Last Order]) - [Avg Order Period])

     

    Then when Difference gets to 0 its time to call,

     

    Seeing it like the above makes it so clear, Thanks Matt, Great Work !!!

     

    Thanks,

     

    J

16 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    9 years ago

    I realise this seems easy, but it is not so easy - hope that makes you feel any better :-).    Power Pivot is not really built to handle relative comparison between rows in a table.  You can do it, but it is not super easy.  But as I think about your problem in a different way, I think there may be an easier way to solve the problem that might meet your needs.  

     

    Break the problem into pieces

     

    Using Adventure Works as an example, you could write measures like this. If you put customer name on a visual (say table) with these measures, I think it will give you what you need.

     

    Last Order=CALCULATE(LASTDATE(Sales[OrderDate]),VALUES(Customers[CustomerKey]))

    First Order=CALCULATE(FIRSTDATE(Sales[OrderDate]),VALUES(Customers[CustomerKey]))

    Total Orders=DISTINCTCOUNT(Sales[SalesOrderNumber])

    First to Last Date period=[Last Order] - [First Order]

    Edit (sorry, previous formula was wrong):  Avg Order Period=divide([First to Last Date period],[Total Orders] -1 )

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks, ill give this a try tonight / tomorrow evening and let you know how i get on. 

       

      Hopefully fix it! Been trying different ways for a few weeks now without sucsess 

       

      Will keep you posted ASAP, 

       

      Thanks, 

       

      Josh

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Datatouille

         

        I also saw your responce as a mention but ill also try that and let everyone know the calculation and if it works or not, 

         

        Thanks for the help guys, 

         

        Cheers, 

         

        Josh

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      the measures seem to be working,

       

      Just need to include a daterange in the measures cause i have 13 years worth of data, but realistically a 4 year calculations should be enough to keep the information relevant and accurate.

       

      Is it possible to include a daterange / date period wrap in the measure so they only look at the last 4 years?

       

      Thanks for the help though, looking better already!!!

       

      Thanks,

       

      Josh

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Also the First Date Measure First Order=CALCULATE(FIRSTDATE(Sales[OrderDate]),VALUES(Customers[CustomerKey]))

       

      That is showing the same date for a lot of customers,

       

       

      But i know these customer wont have ordered all on the 01/01/2013... Yet the number of orders is most likely correct,

       

      Is there a way to have the first order be for a customer based on there unique ID and not just the first date in the dataset?

       

      Thanks again,

       

      Josh