Condition in Set expression

[gasparbenito]

Hi all,

Can someone help me with this?

I have an Excel file with a list of costs per line, according to the product (sale).

And in the script I have calculated that each of those lines, multiply that cost by the boxes (unit of sale) sold.

Right now, there is only one cost per reference (line), regardless of the year.

I want to add the year field, as the cost per product varies each year, and now applies the same cost, regardless of the selected year.

The current formula is:

ApplyMap ('Map_CosteCaja', Mid (% Key_Articulo, FindOneOf (% Key_Articulo, '123456789')), 0) * #Cajas as #CosteVentas

How can I add to this formula the option that if for example, if I select 2017 it uses a cost and if I select 2018 it uses a different cost?

For example: 

Referencia	Año	Coste
100001	2017	1,25
100001	2018	1,35

Thanks in advance!

Gaspar

[rubenmarin · Accepted solution]

Hi Gaspar,

The 2nd parameter of applymap uis the value to search, add a concatenated key using Year, and also include year in the first field on the mapping table Map_CosteCaja.

ApplyMap ('Map_CosteCaja', YearField&'#'&Mid (% Key_Articulo, FindOneOf (% Key_Articulo, '123456789')), 0) * #Cajas as #CosteVentas

[gasparbenito]

Hi Ruben,

It works, I found the field in the Fact table and I have joined it following your advice YearField & '#' &

Many thanks!