Comparing before & after the date of change

[#1]

I am trying to get, by salesperson, how is the sales growth rate comparing before and after the promotion date.

Sample Table 1: Sales Person Master List

Sales ID	Sales Person Name	Promotion_Date
1	Alex	1/4/2014
2	Thomas	1/4/2014
3	Rachel	1/5/2014
4	Lily	1/6/2014
5	Lee	1/6/2014

Sample Table 2: Sales

Date	Month	Sales ID	Sales_Amount
1/3/2014	3	1	2
10/3/2014	3	1	6
1/4/2014	4	1	5
2/5/2014	5	1	10
4/6/2014	6	1	4
1/2/2014	2	2	6

I would like to achieve something like this:

Where

Before = 3 months average before promotion date

After = 3 months average from promotion date

Sales Person Name	Before	After	Growth Rate
Alex	8	6	-25%

I have tried following formula for "Before" but it only works only if I bring in Month into the results table:

if(Month >=Month(AddMonths(Promotion_Date,-3)) and Month < Month(Promotion_Date),

SUM( Sales_Amount) )

What would be the correct formula for this?

[SunilChauhan]

see the attched file

hope this helps

[chematos]

Before:

SUM({<Date={'>=$(=Addmonths(Promotion_Date,-3) <=only(Date)'}>} Sales_Amount)

After:

SUM({<Date={'>=only(Date) <=$(=Addmonths(Promotion_Date,+3)'}>} Sales_Amount)

Growth Rate:

(After*Before)-1

Also, you could transform to numerics all your date fields and add or sustract 90 days to calculate before or after 3 months, instead using Date type fields

[maxgro]

before

sum(if(Date>=AddMonths(Promotion_Date,-3) and Date<Promotion_Date,Sales_Amount))

after

sum(if(Date>Promotion_Date and Date<=AddMonths(Promotion_Date,+3),Sales_Amount))