Running Total for Months With Different Percentage Rates

[saralyndsay]

Hello, 

I am trying to calculate out a rebate dollar amount, that can be calculated using a date slicer. 

I have a table set up with the rebate % by month, using the first day of the month as the date. We will call this the rebate table.

Date	Month	Brand	Quarter Rebate %
3/1/2020	3	KONG	0.05
2/1/2020	2	KONG	0.04

I have another table that purchases by date range and sku. We will call it the Purchases table.

Date	Sku	Purchases
3/1/2020	1	20000
3/2/2020	2	40000
2/1/2020	3	10000
2/3/2020	4	20000

These tables are linked via a sku/brand linking table as well as through a general calendar table. 

What I need to accomplish is the following. I need the sum of all the purchases in the purchases table for the month to be multiplied by the Quarter Rebate % for that month.

I have been able to accomplish this for a month by monty basis with the formula: 

Quarter Rebate Calc = calculate(sumx(filter(Rebates,Rebates[Month]=lookupvalue('Calendar'[Month],'Calendar'[Date],Rebates[Date])),Rebates[Quarter Rebate %]))*[Purchases]

The issue is, if I am trying to look at the two months together, it is summing the rebate % and multiplying it by the purchases. Ie it is multiplying by 9%. 

Ideally the result would be 4200 (60000 total in March x 0.05 + 30000 total in Feb x 0.04). But the result I am getting is 8100 (90000 x 0.09).

Any help would be appreciated! 

[amitchandak]

All new columns in table 2. Add additional condition as per need

max rebate date= maxx(filter(table1,month(table1[Date])=month(table2[Date])),table1[Date])

rebate =maxx(filter(table1,month(table1[Date])=month(table2[max rebate date])),table1[Quarter Rebate %])

Quarter Rebate Calc = [rebate]*[Purchases]

[saralyndsay]

@amitchandak If I wanted to also have the condition by the Brand as shown in table 1, and there is a separate table with sku and brand in it, how would I add that in? 

[saralyndsay · Accepted solution]

@amitchandak Nevermind I think I got it! Thanks for your help.