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

markus_zhang's avatar
markus_zhang
Icon for Advocate III rankAdvocate 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
Icon for Community Champion rankCommunity 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
    Icon for Advocate III rankAdvocate 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]