Sales Within 10 Days of Main Purchase

[eliwaite]

I am trying to determine the additional sales someone makes within 10 days of their main purchase. Here is a link to a sample spreadsheet. In this spreadsheet you will find a sales table (within the PowerPivot model you will also find a calendar table).
In this example a Bike purchase is considered the main purchase and I want to know how much they bought with in the next 10 days. So the correct outcome would be:
Customer ID 5: $50 because they bought a bike on 1/5/2017 and then on 1/10/2017 they bought a seat for $50. They also bought pedals on 2/15/2017, but that is more than 10 days pasted their bike sale so it is not counted.
Customer ID 7: $15 because they bought a bike on 1/7/2017 and then bought a $5 bell on 1/9/2017. They bought another bike on 3/1/2017 and on that same day bought a basket for $10, thus $10 plus $5 is $15.

 

[Matt Allington]

So what is the mathematical definition of "considered the main purchase"? I get it, but that won't cut it in a formula

 

[eliwaite]

Hi Matt,

For the main purchase, this is when the product category = "Bike".

I have figured out a calculated column, which appears to do the trick:

=CALCULATE(SUM(Sales[Price]),Sales[Product Category]<>"Bike",FILTER(ALL(Sales),Sales[Customer ID]=EARLIER(Sales[Customer ID])&&EARLIER(Sales[Product Category])="Bike"&&Sales[Date]<=EARLIER(Sales[Date])+10&&Sales[Date]>=EARLIER(Sales[Date])))
​

And then I use a simple Sum for a calculated measure to wrap it up in a bow.


This is working for me. However if you see an error or know a more efficient way of doing this, I would love to learn more.

 

[Matt Allington]

If it's working, then great. It is a relatively complex runtime calc, so I don't know how performant a measure would be anyway

 

[Ozeroth]

Hi eliwate & Matt,

Uploaded an edited version of your file here showing how this can be done with a measure:
https://www.dropbox.com/s/fb0arngcr0q7g5a/Sales Within 10 Days of Main Purchase Owen edit.xlsx?dl=0

1. I redefined Revenue := SUM ( Sales[Price] )
   
2. Also corrected the Sales-Calendar relationship direction. It appears Sales was on the 1-side instead of Calendar.
3. Then the Additional Sales measure is:

[B]Revenue Additional Sales Within 10 Days[/B]
=
CALCULATE (
    [Revenue],
[COLOR=#008000]    KEEPFILTERS (
        SUMMARIZE (
            GENERATE (
                CALCULATETABLE (
                    SUMMARIZE ( Sales, Sales[Date], Sales[Customer ID] ),
                    Sales[Product Category] = "Bike",
                    ALL ( Sales )
                ),
                DATESINPERIOD ( 'Calendar'[Date], Sales[Date], 11, DAY )
            ),
            'Calendar'[Date],
            Sales[Customer ID]
        )
    ),[/COLOR]
    [COLOR=#ff0000]KEEPFILTERS ( Sales[Product Category] <> "Bike" )[/COLOR]
)

The green argument within CALCULATE pairs up each customer with a list of dates corresponding to each bike sale (that ever occurred, due to the ALL ( Sales ) ) plus the subsequent 10 days (11 dates in total).
The red argument excludes bikes.
I wrapped these arguments in KEEPFILTERS in both cases to intersect with the original filter context.

It works with your sample data but might need further testing with your full model.

Cheers,
Owen

 

[Ozeroth]

Slight revision:

It makes more sense to change ALL ( Sales ) to ALLEXCEPT ( Sales, Sales[Customer ID] ). Result is the same, just generates the customer/date pairs for customers in current context only.

Revenue Additional Sales Within 10 Days=
CALCULATE (
[COLOR=#006400]    [Revenue],[/COLOR]
[COLOR=#006400]    KEEPFILTERS ([/COLOR]
[COLOR=#006400]        SUMMARIZE ([/COLOR]
[COLOR=#006400]            GENERATE ([/COLOR]
[COLOR=#006400]                CALCULATETABLE ([/COLOR]
[COLOR=#006400]                    SUMMARIZE ( Sales, Sales[Date], Sales[Customer ID] ),[/COLOR]
[COLOR=#006400]                    Sales[Product Category] = "Bike",[/COLOR]
[COLOR=#006400]                    ALLEXCEPT ( Sales, Sales[Customer ID] )[/COLOR]
[COLOR=#006400]                ),[/COLOR]
[COLOR=#006400]                DATESINPERIOD ( 'Calendar'[Date], Sales[Date], 11, DAY )[/COLOR]
[COLOR=#006400]            ),[/COLOR]
[COLOR=#006400]            'Calendar'[Date],[/COLOR]
[COLOR=#006400]            Sales[Customer ID][/COLOR]
[COLOR=#006400]        )[/COLOR]
[COLOR=#006400]    ),[/COLOR]
[COLOR=#ff0000]    KEEPFILTERS ( Sales[Product Category] <> "Bike" )[/COLOR]
)

 

[Matt Allington]

You do my head in Owen . In a nice way