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

MattAllington's avatar
MattAllington
Community 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago
    Or is the -1 in the average day per order calculation to account for that false input on the first order been on the 01/03/2017? As they will have had -1 orders since that first date to make it correct... The only downside there is the time period it defines the average on is incorrect as they may have ordered 10 times in 10 months making the average 30 days and not 608 days which is what would currently show for the 10 orders in 10 months because the time frame is 5748 days not 300 days. If that makes sense or am I reading it wrong? Thanks again, Josh
  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    You beauty Matt!  I've just spent two hours trying to crack that - so happy I found your post!

  • markus_zhang's avatar
    markus_zhang
    Advocate III
    7 years ago

    Hi Matt,

    Sorry I'm lat to the party, but I noticed tht you mentioned:

     

    Power Pivot is not really built to handle relative comparison between rows in a table. 

     

    However such requirement is pretty much everything I'm doing with a data dump (e.g. find difference between two rows based on same value of some column). Does it mean that I should re-format the raw dump to better suit Power Pivot?

     

    Thanks in advance!

    • MattAllington's avatar
      MattAllington
      Community Champion
      7 years ago

      Possibly, it is hard to say without seeing the details.  Generally the order of rows in a DB are not important.  If you need to compare change in status between rows (for example), then you can do it in DAX and or restructure the data.  Both approaches have their strengths and weaknesses.  It depends.

      • markus_zhang's avatar
        markus_zhang
        Advocate III
        7 years ago

        Thanks Matt, I found it particularly to do anything for row operations without a convoluted formula and I'm wondering if I'm choosing the wrong tool.

         

        For example:

        I have the following table:

         

        Transaction ID | Event Type | IP Address | Datetime | Email Address |

         

        I have a bunch of these transactions, and an [Event Type] could be a Purchase (transfer real money for virtual coins), or a Virtual Purchase (transfer virtual coins for web contents). Now for each Virtual Purchase I'm missing the email address, so I would like to set up a Calculated Column, say named [Possible Email Address] for each row that has [Event Type] = "Virtual Purchase", with the following rule:

         

        Search all previous rows, find a row that has a matching IP Address, and use the [Email Address] of that row to populate [Possible Email Address] of this row.

         

        Example:

        Transaction ID | Event Type | IP Address | Datetime | Email Address |

        100001 | Purchase | 1.1.1.1 | 2018-01-11 | [email protected]

        100002 | Purchase | 1.4.5.6 | 2018-01-11 | [email protected]

        ...(after many rows)

        100356 | Virtual Purchase | 1.1.1.1 | 2018-01-13 | UNKNOWN

         

        So after adding the calculated column Transaction ID 100356 should become this:

         

        100356 | Virtual Purchase | 1.1.1.1 | 2018-01-13 | [email protected]

  • Fghandour18's avatar
    Fghandour18
    New Member
    4 years ago

     

    Hi Matt,

     

    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!