Raw Material Price

[Anonymous]

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?

[Sahir_Maharaj · Accepted solution]

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

[Anonymous]

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!