Chose wich value to use in calculation

[bnjmnnl]

Hello all, 

I'm facing quite the challange to make a calculation. Below is a table of an example of my data:

ID	CostType	amount	currrency	Validfrom	Validto	Calculatefrom
1	TRA	1000	EUR	01/01/24	31/12/24
1	HNDL	50	EUR	01/01/24	31/12/24
1	ADMIN	80	EUR	01/01/24	31/12/24
1	TRA	1200	EUR	01/01/25	31/12/25
1	HNDL	60	EUR	01/01/25	31/12/25
1	ADMIN	90	EUR	01/01/25	31/12/25
1	ADR	5	%	01/01/24	01/01/25	TRA
2	TRA	1100	EUR	01/01/24	01/01/25
2	HNDL	100	EUR	01/01/24	01/01/25
2	ADMIN	20	EUR	01/01/24	01/01/25

As you can see the ADR type for ID 1 is calculated based on the TRA type of ID 1. However, the validity dates don't have to be the same. As today is in the year 2024, the ADR must be 5% * 1000 and when the year 2025 arrives it will be 5% * 1100. 
This calculation will provide the user with the following:
User makes a selection based on routes an weights of container transports and will be provided with the possible options including the prices. In this calculation the ADR costs must be calculated. Is there any way to achieve this? If more info is needed, please ask me! 

Thank you in advance!

[hnguy71]

Hi @bnjmnnl ,

It's possible but I think your table has some holes that it may need to get filled for this to be accurate. Based on the information provided you'll most definitely need a disconnected table for user input. Here's a modified table that would better fit your criteria:

ID	CostType	amount	type	currrency	Validfrom	Validto	Calculatefrom
1	TRA	1000	currency	EUR	1/1/2024	31/12/24
1	HNDL	50	currency	EUR	1/1/2024	31/12/24
1	ADMIN	80	currency	EUR	1/1/2024	31/12/24
1	TRA	1200	currency	EUR	1/1/2025	31/12/25
1	HNDL	60	currency	EUR	1/1/2025	31/12/25
1	ADMIN	90	currency	EUR	1/1/2025	31/12/25
1	ADR	5	percent		1/1/2024	1/1/2025	TRA
2	TRA	1100	currency	EUR	1/1/2024	1/1/2025
2	HNDL	100	currency	EUR	1/1/2024	1/1/2025
2	ADMIN	20	currency	EUR	1/1/2024	1/1/2025

Then you'd just have to return the right value for the selected cost type (assuming you always want to return the latest available record that meets the latest date of the year):

SelectedAmount = 

VAR _cDate = TODAY()
VAR _CostType = SELECTEDVALUE(Disconnected[CostType])
VAR _vType = SELECTEDVALUE(Disconnected[type])

VAR _Amount = SWITCH( _vType,
    "currency", SELECTEDVALUE(Disconnected[amount]),
    "percent", 
        VAR _FromType = SELECTEDVALUE(Disconnected[Calculatefrom])
        VAR _GetAmt = IF(NOT ISBLANK(_FromType), CALCULATE(MAX(Disconnected[amount]), Disconnected[CostType] = _FromType, Disconnected[Validto] <= _cDate) )
        RETURN
        _GetAmt
)

RETURN _Amount

 
If you wanted to also grab the currency available for that same record, you'd add to the current measure or create a different one for specifically for currency conversions using a similar switch statement:

VAR _Currency = SWITCH( _vType,
    "currency", SELECTEDVALUE(Disconnected[currrency]),
    "percent", 
        VAR _FromType = SELECTEDVALUE(Disconnected[Calculatefrom])
        VAR _GetCurrency = IF(NOT ISBLANK(_FromType), CALCULATE(MAX(Disconnected[amount]), Disconnected[CostType] = _FromType, Disconnected[Validto] <= _cDate) )
        RETURN
        _GetCurrency
)

[bnjmnnl]

Thank you for your reaction! However, it is not always the latest date that is de correct one. Sometimes prices are set for periods in the near future, for example a price is already discussed for the period 1-1-2025 till 1-1-2026. In this case tis will be the latest available, however we still want to use the prices that are valid now and not in the near future. 
Isn't there a way to do something like a VLOOKUP (exept then for dax or power query) to have the costs with % and are based on other values placed on the same row as the costs they need to be calculated with? The values it needs to match on are weight, containertype, ID and if it falls in the validty date. The calculatefrom column matches a value in the costtype column. 

I have already split the table in two tables, one for valutas and one for %

Thank you in advance.