Purchase price based on the date of the related line

[RubenIdx]

Good day, I have a model with which I evaluate the cost of producing certain items I have the tables "Structures" that contains the detail of the production orders (produced units /inputs used) and the table "Purchasing" which contains the detail of the purchases of inputs (quantities / amounts / prices) and I require to obtain the price of the last purchase of each item according to the date of its production as shown in the images:

For example, the product AJON-011 for production of 03/12/2019 would take the price of 30/11/2019, for the production of 15/01/2020 the price corresponding to it is 14/01/2020 and so on with all products

I tried to use the following measure however I can't find the right logic to achieve the expected result.

Ult CompraFe = 
VAR __id = MAX (Compras[KeyProducto] )
VAR __date = CALCULATE ( MAX( Compras[Fecha] ), ALLSELECTED ( Compras ),  Compras[KeyProducto] = __id ) 
RETURN CALCULATE ( MAX ( Compras[Unit] ), VALUES ( Compras[KeyProducto] ), Compras[KeyProducto] = __id, Compras[Fecha] = __date )

I appreciate your help in advance.

[parry2k · Accepted solution]

@RubenIdx somewhere your data t ype is not correct, check data type of all related columns used in this measure. I cannot tell from here where is the issue, but it is surely data type issue.

[parry2k]

@RubenIdx can you paste the sample data instead of images.

[parry2k]

@RubenIdx try this measure

Production Value = 
VAR __p = SELECTEDVALUE ( Production[Id] )
VAR __d = SELECTEDVALUE ( Production[Date] )
VAR __maxPurchaseDate = CALCULATE ( MAX ( Purchase[Date] ), Purchase[Id] = __p, Purchase[Date] <= __d ) 
RETURN 
CALCULATE ( MAX ( Purchase[Rate] ), Purchase[Id] = __p, Purchase[Date] = __maxPurchaseDate ) * SUM ( Production[Prod] )

[RubenIdx]

I have tested the measurement and at the time of writing it does not mark me errors however when adding it to my visulation I send this message

Error Message:
MdxScript(Model) (8, 93) Calculation error in measure 'Structures'[Ult PurchaseFe]: DAX comparison operations do not support comparison values of type Text with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.

I have reviewed my tables and date columns if they are as date.

[v-yiruan-msft · Accepted solution]

Hi @RubenIdx ,

Please try to update the formula as below:

Ult CompraFe=
var kprod = CALCULATE(MAX('BDimensiones'[Kprod]), FILTER('BDimensiones','BDimensiones'[Prod]=SELECTEDVALUE ('DimEspejo'[Prod] )))
var Edate= SELECTEDVALUE('Estructuras'[Fecha])
var maxComDate = CALCULATE ( MAX ( 'Compras'[Fecha] ), Compras[KeyProducto] =kprod, 'Compras'[Fecha] <=Edate) 
RETURN 
CALCULATE ( MAX ( 'Compras'[Unit]), Compras[KeyProducto]= kprod, 'Compras'[Fecha] = maxComDate) 

Best Regards

Rena

[parry2k · Accepted solution]

@RubenIdx somewhere your data t ype is not correct, check data type of all related columns used in this measure. I cannot tell from here where is the issue, but it is surely data type issue.

[RubenIdx]

Hello! I have modified the relationships of the tables and the measure you have provided me worked perfectly I share the model with the suggested measures already implemented.

https://1drv.ms/u/s!Aux4yKXbJDBchD_YJvKc8P-JOb2p?e=m5cavq

[v-yiruan-msft]

Hi @RubenIdx ,

If the problem has been resolved, could you please mark the helpful post as Answered? It will help other members in the community find the solution easily if they face the similar problem with you. Thank you.

Best Regards

Rena

[RubenIdx]

Thank you! attached discharge link for the model.

https://1drv.ms/u/s!Aux4yKXbJDBchD5Ck4jQKVhSPFMq?e=0zqfN5