topic Re: Raw Material Price in DAX Commands and Tips

Raw Material Price

Anonymous — Thu, 21 Dec 2023 16:39:34 GMT

Hi guys,

I have a measure (amount) whose define my amount of raw material and a table with this columns

-raw material

-price

-amount

-date of purchase

What I need is another measure to define my price. For example, if my measure amount is 5000 for material 1 and my table is like:

raw material	price	amount	date of purchase
material1	2,9	2000	18/dec/2023
material1	1,8	3500	20/dec/2023
material2	3,4	1000	12/dec/2023
material2	0,8	1500	10/oct/2023

I need to order my table by date of purchase descendent and calculate something like this:

3500*1,8 + (1500)*2,9 = 10650

Important, my amount is 5000, so I cant to surpass this amount, that's why I used 1500 instead of 2000 to multiply by 2,9.

How can I do this measure?

Re: Raw Material Price

Sahir_Maharaj — Thu, 21 Dec 2023 18:47:59 GMT

Hello Anonymous,

Can you please try this:

Calculate Price = VAR TotalAmountNeeded = 5000 VAR CumulativeTable = ADDCOLUMNS( FILTER( 'YourTable', 'YourTable'[raw material] = "material1" ), "CumulativeAmount", CALCULATE( SUM('YourTable'[amount]), FILTER( ALL('YourTable'), 'YourTable'[date of purchase] <= EARLIER('YourTable'[date of purchase]) && 'YourTable'[raw material] = EARLIER('YourTable'[raw material]) ) ) ) VAR Result = SUMX( CumulativeTable, IF( [CumulativeAmount] <= TotalAmountNeeded, [amount] * [price], MAX(0, TotalAmountNeeded - [CumulativeAmount] + [amount]) * [price] ) ) RETURN Result

Re: Raw Material Price

Anonymous — Fri, 22 Dec 2023 18:32:15 GMT

Thank you very much, Sahir_Maharaj!

It is just what I needed, I just made some little changes, but the main logic is what you did!