Calculating Sales 3 Months Before and 3 Months After an Activity has Occurred

[Anonymous]

Hello,

For this problem, I have two tables of interest:

Table 1: Contains sales data pertaining to distributors sales in certain product categories.

Table 2: Contains distributors names, product categories, and Start Date and End Date of activities performed against these categories.

TABLE 1:

Distributor Name	Product Sub-Category	Year Month	Sales
Test_distributor	Tennis balls	2021-01	$100
Test_distributor	Tennis balls	2021-02	$50
Test_distributor	Tennis balls	2021-03	$150
Test_distributor	Tennis balls	2021-04	$250
Test_distributor	Tennis balls	2021-05	$350
Test_distributor	Tennis balls	2021-06	$450
Test_distributor	Tennis balls	2021-07	$550
Test_distributor	Tennis balls	2021-08	$650
Test_distributor	Tennis balls	2021-09	$750
Test_distributor	Tennis balls	2021-10	$800

TABLE 2:

Distributor Name	Product Sub-category	Activity Launch Date	Activity End Date
Test_distributor	Tennis Balls	2021-05	2021-07

GOAL: See sales for test_distributor in Tennis Balls for dateframe 2021-02: 2021-04 and sales for 2021-8: 2021-10

(Sum of sales for 3 months before the activity occurred, and sum of sales for the 3 months after the activity occurred.)

RESULTING TABLE:

Distributor Name	Product Sub-Category	Activity Launch Date	Activity End Date	Sales 2 Months Before Activity	Sales 2 Months After Activity
Test_distributor	Tennis Balls	2021-05	2021-07	50+150+250=$450	$650+$750 +$800= $2,200

This is just one example. I have many distributors with activities pertaining to increasing product sub-category sales growth. If we can find a solution to this example, I am fairly confident it will scale though.

Thanks so much for any/all help!!

Joel

[amitchandak · Accepted solution]

@Anonymous , Mark your month year column as a date or create a date from those. then you can have two new columns in table 2

New column =
sumx(filter(Table1, Table1[Distributor Name] = Table2[Distributor Name] && Table1[Date]> eomonth([Activity Launch Date],-3) && Table1[Date]<= eomonth([Activity Launch Date],-1) ),[Sales])

New column 2 =
sumx(filter(Table1, Table1[Distributor Name] = Table2[Distributor Name] && Table1[Date]> eomonth([Activity End Date],0) && Table1[Date]<= eomonth([Activity End Date],2) ),[Sales])

[Anonymous]

@amitchandak Thanks so much! I feel much closer to a solution. I am running into an issue though. See below screenshot:

To the left of the Business Unit Campaign column is the Distributor Name column (left it out for privacy reasons). It seems that this column is summing sales based on distributor name for activity launch dates. Not taking into account the campaign at hand. I'd like to know the sales for the distributor name for the BU campaign item (both factors). Any recommendations?

Thanks again!!

Joel

[Anonymous]

@amitchandak Sorry! Didn't look at the formula hard enough haha. I figured it out myself by adding one more && statement to compare the BU Campaigns. You're awesome!!! Thanks again!