DAX lookup within date and with same ID

[bnjmnnl]

Hello all, 

For making a calculation I need some help of the experts on here haha. 

I have two tables that represent the costs lines for certain transports. Table 1 shows the lines with currencies and table 2 shows the lines with %  and are based on certain values of table 1. I will show an example below:

Table 1
Contract	Costtype	Object	Amount	Currency	RESULT (calculate)
1	tra	234	150	EUR	15
1	admin	234	10	EUR	0
1	handling	234	50	EUR	0
2	tra	222	150	EUR	15
3	tra	211	130	EUR	0
3	admin	211	12	EUR	15
Table 2
Contract	Costtype	Object	Amount	Currency	Calculatefrom
1	fuel	234	15	%	tra
2	fuel	222	15	%	tra
3	fuel	211	15	%	admin

The solution must result in the resultcolumn descirbed in Table 1. The percentage amount must be placed in the right row based on a link between costtype from table1 and calculatefrom from table 2. This wouldn't be too hard, but there is another issue. The amount have certain validities, based on a startdate column and an enddate column. The fuel costs don't need to have the exact same validity dates as the other ones within the same contract. I want to have the right percentage (from table 2) that was valid between the dates of the cosstype in table 1. 
Im not sure if this will be possible, otherwise please provide me the solution when there are no more dates but only the latest valid ones. 

Thank you in advance! If more info needed, please ask me!!!!

[bhanu_gautam]

@bnjmnnl   , You can create a calculated column in Table 1 for this 

Result =
VAR CurrentContract = Table1[Contract]
VAR CurrentObject = Table1[Object]
VAR CurrentCostType = Table1[Costtype]
VAR CurrentAmount = Table1[Amount]
VAR RelatedRow =
CALCULATETABLE(
Table2,
Table2[Contract] = CurrentContract,
Table2[Object] = CurrentObject,
Table2[Calculatefrom] = CurrentCostType
)
VAR Percentage = MAXX(RelatedRow, Table2[Amount])
RETURN
IF(
Table1[Costtype] = "tra",
CurrentAmount * (Percentage / 100),
0
)

If you need to consider date validity, you can add additional conditions to the CALCULATETABLE function to filter based on the date range.

[bnjmnnl]

Hello, 

Thank you for tehg reply, 
However the caluclated costs don't need to be "tra". It can be much more than tra

[bhanu_gautam · Accepted solution]

Ok then updated the measure

RESULT =
VAR CurrentContract = Table1[Contract]
VAR CurrentCosttype = Table1[Costtype]
VAR CurrentObject = Table1[Object]
VAR CurrentAmount = Table1[Amount]
VAR CurrentStartDate = Table1[startdate]
VAR CurrentEndDate = Table1[enddate]

VAR RelatedRow =
CALCULATETABLE(
Table2,
Table2[Contract] = CurrentContract,
Table2[Object] = CurrentObject,
Table2[Calculatefrom] = CurrentCosttype,
Table2[startdate] <= CurrentStartDate,
Table2[enddate] >= CurrentEndDate
)

VAR Percentage = MAXX(RelatedRow, Table2[Amount])

RETURN
IF(
ISBLANK(Percentage),
0,
CurrentAmount * Percentage / 100
)