Home Forums Power Pivot First sale to customer

Tagged: 

This topic contains 2 replies, has 2 voices, and was last updated by  tomallan 2 years, 7 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #4817

    IanR
    Participant
    • Started: 8
    • Replies: 7
    • Total: 15

    <span style=”color: #000000; font-family: Calibri;”>Hi all,</span>

    <span style=”color: #000000; font-family: Calibri;”>I am trying to find out if a sale is the first sale to a particular customer using the opportunities table from our CRM. Each record will have a customer ID and, if there was a sale, the status will be “Won” and the ActualCloseDate will have a non-blank value. Can I use RANKX or something similar to populate a calculated column with a number than represents whether the sale was the first, second, third etc. to that particular customer? I was hoping I could use something like RANKX (opportunity WHERE Customer = this customer and Status = “Won”, Rank by ActaulCloseDate DESC).</span>

    Is any of this possible – is there another way?

    Thanks

    Ian

     

    #4819

    IanR
    Participant
    • Started: 8
    • Replies: 7
    • Total: 15

    Or to put it another way, I’m trying to do something equivalent to what a  ROW_NUMBER OVER (PARTITION BY ..) would achieve in SQL.

    Ian

    #4828

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2552
    • Total: 2559

    Ian,

    I have seen this done before through a calculated column in the sales table and the gist of the formula was counting distinct sales order numbers ( or if there is one row per order, counting rows) less than the sales order number of the row being evaluated and for the same customer ID. Formula used the EARLIER function twice, once on customer ID (to capture the current row’s customer ID) and once on order number (to capture the current row’s order number).

    Power Query could also be used to sort and assign sequence numbers (I have done this more than once).

    If possible, since your data may come from a SQL Server, using a ROW_NUMBER OVER (PARTITION BY ..) in your SQL query may be the fastest and easiest solution.

    Tom

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.