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....
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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

 

Dont get me wrong though for our longest standing customers its working well,

 

Just how to make it see "Their" first order and then drop the -1 on the Average?

 

Thanks though this is excellent if it will work!

 

Was almost going to pull it into excel manually do the averages and then have a relationship between two data tables to show it in one graph visual.

 

Thanks,

 

Josh

MattAllington's avatar
MattAllington
Community Champion
9 years ago

You have lots of replies and thoughts here and I am not really sure the current status of open issues. Can you do a single reply explaining what open issues you have?

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    • Fghandour18's avatar
      Fghandour18
      New Member
      4 years ago

      Hi Josh,

       

      I realize I am following up after 5 years... but any chance you have a demo file you can upload? Trying to solve a simillar problem and getting no results.

       

      thanks!