How do you write a measure to calculate totals based on an "active" percentage value?

[arpost]

Greetings, PBI community! I am needing to calculate totals based on a percentage that was "active" during the specified date range and am struggling to figure out the scenario. Figure it'd be easier for me to "show" rather than just tell, so here goes.

I have a table containing data as follows:

Sales

Date	Value
1/1/2014	$100
2/1/2014	$500
7/1/2014	$600
5/1/2015	$200

I also have a table listing the different commission % rates that were in effect at a particular date.

Commission Rates

Date	Percentage
1/1/2013	10%
6/1/2014	15%
1/1/2015	20%

What I'm wanting to do is create a measure that shows the total commission $ for a given range of time based on the % rate that was in effect.

For example, with the sales data, this is the equivalent result in the form of a table:

Date	Value	Commission	Rate
1/1/2014	$100	$10	10%
2/1/2014	$500	$50	10%
7/1/2014	$500	$75	15%
5/1/2015	$200	$100	20%

Ultimately, I want to "roll up" the calculations for a given year, so the total for 2014 would be $135 ($10 + $50 +$75).

[arpost]

Greetings, @Ashish_Mathur  and @ryan_mayu! Thank y'all both for responding. These are great solutions, but I've hit a roadblock - the datasource is using DirectQuery, which doesn't support either MAXX or CALCULATE in calculated columns! 😞 Any ideas on how this can be accomplished solely using measures?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[ryan_mayu]

@arpost 

you can create a column to calculate commission

commission = 
VAR _date=maxx(FILTER('Commission Rates','Commission Rates'[Date]<=Sales[Date]),'Commission Rates'[Date])
VAR _per=maxx(FILTER('Commission Rates','Commission Rates'[Date]=_date),'Commission Rates'[Percentage])
return Sales[Value]*_per

then create a date table

pls see the attachment below