locked
Help needed with EARLIER() to calculate customers last order per date period RRS feed

  • Question

  • Hi,

    Our customers return year over year and we are trying to understand their behaviour within each given year, more precisely we want to understand at what point in the year they are placing their final order before coming back the following year. 

    With that in mind I am trying to create a table that illustrates, per date period (week, quarter, month - this will be chosen with a slicer), a count of the number of orders placed which turned out to be the customers final order for that year.

    The table below illustrates example data which I am using to try and calculate this. This table shows only a single customer's orders, and is limited to only one fiscal year to make it easier to understand.

    I have three primary columns here that I am using as helpers to achieve my final result.

    • cust_order_count_cumulative - this is the total number of orders the client has placed at that point in time.
    • cust_maxorder_lifetime - this is the total number of orders the customer has placed in their entire lifetime across multiple years.

    The column cust_maxorder_daterangeis the one that I am trying to figure out but before explaining I should first illustrate my logic for calculating whether an order is a customers final order in a fiscal year is as follows...

    - Identify the max number of orders the customer placed in the chosen date range (read Fiscal Year)

    - Per row compare the cumulative count against the max count, if they are equal it is the customers last order, if they are different it's not.

    To complete this I therefore need a calculated column which is showing me the MAX order count for that customer per row per period.  This is what cust_maxorder_daterange should be.

    In the example above this column should therefore be 7 all the way down. As you can see I have got the same pattern working with cust_maxorder_lifetime using the formula below, however this calculates the entire table and ignores any date range applied by a filter. I cannot work out how to modify the formula to work with said filter.

    =CALCULATE(MAX('Q1 Data Set'[cust_order_count_cumulative]),ALL('Q1 Data Set'),'Q1 Data Set'[k1_customer_id]=EARLIER('Q1 Data Set'[k1_customer_id]))

    An example of how the final table should look is below:



    This has been plaguing me for literally ours so any help would be much appreciated. I also have a horrible feeling that my approach is very long-winded so if there are far simpler ways please let me know even if it means significantly changing my tables.

    Thanks in advance.









    • Edited by Maracles Thursday, April 24, 2014 6:39 PM
    Thursday, April 24, 2014 6:35 PM

Answers

All replies

  • Hi, you've got a couple of issues with the formula the biggest being that EARLIER() needs to be used in an iterator so should be contained in a FILTER() here. Suspect you also don't need the ALL().

    That said, I think you might be over complicating the problem. Based on you output at the bottom, all you really need to do this is a flag on each order to say whether or not that order date matches the latest order date in that year for that customer. Assuming you add a column for year you could use something like:

    =
    IF (
        [Date]
            = CALCULATE (
                MAX ( [Date] ),
                FILTER (
                    'Q3 Data Set',
                    [Customer ID] = EARLIER ( [Customer ID] )
                        && [Year] = EARLIER ( [Year] )
                )
            ),
        "Y",
        "N"
    )



    Jacob | Please mark helpful posts and answers


    Thursday, April 24, 2014 7:31 PM
  • Hi Jacob,

    Thanks for your response, using the date method was something that I had considered but the problem I ran into is that I wanted the slicing of the data to be dynamic. As such the date_order field above is related to a custom date table I have and I use custom fields such as fiscal year, fiscal quarter, week, month etc to slice my data.

    I couldn't figure out how to utilise this rather than setting up a calculated column for each date range I wanted.

    Is this possible?

    Thanks,

    Thursday, April 24, 2014 7:40 PM
  • Then I don't think a solution using calculated columns is going to get to a dynamic version as the values are fixed on calculation and unable to react to changes in Pivots/Slicers.

    A measure solution should be possible, probably using a second, possibly 'disconnected', calendar. Happy to take a look if you had a bit more data that you could share via OneDrive/DropBox etc - doesn't have to be real just reflective of your problem.


    Jacob | Please mark helpful posts and answers

    Thursday, April 24, 2014 9:02 PM
  • Hi Maracles,

    Did you consider to use a disconnected slicer to handle your different date critera? See here for info on disconnected slicer: http://www.powerpivotpro.com/2013/06/simplifying-time-calculations-and-the-user-experience-using-disconnected-slicers/

    The value from that slicer would replace the [Year]-part in Jacob's formula if I understand your issue correctly.

    Regards,

    Julian


    Julian Wissel | BI for NAV @ http://en.navbi.com | Blog @ blog.navida.eu

    Thursday, April 24, 2014 9:14 PM
  • Thank you both for your replies, Jacob I really appreciate your help and I may come back to you and send a sample workbook, however I am not familiar with the disconnected slicers that Julian has linked to so I will explore those too, they look very useful. There is also a mention of 'The Greatest Formula In The World' which sounds worth looking into.

    I will come back and update the thread tomorrow,  unfortunately I'm not at a PC with PowerPivot installed for the rest of the evening.

    Thanks again.

    Thursday, April 24, 2014 9:24 PM
  • Disconnected Slicer is the way to go but it would need to incorporated into a measure as it wouldn't work in a Calc Column.

    Rob's post is a great starting point but I think your issue is a little more complex.

    Best of luck :-)


    Jacob | Please mark helpful posts and answers

    Thursday, April 24, 2014 9:54 PM
  • Hi Jacob,

    Whilst I'm looking at Disconnected Slicers I thought I might take you up on your offer of sending a sample workbook. I couldn't find a way of PM'ing you but if you send me some contact details I can send it across.

    Thanks,
    James

    Friday, April 25, 2014 1:00 PM
  • My linkedin details are on the profile page.

    Jacob | Please mark helpful posts and answers

    Friday, April 25, 2014 1:30 PM