Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Raw Material Price

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 materialpriceamountdate of purchase
material12,9200018/dec/2023
material11,8350020/dec/2023
material23,4100012/dec/2023
material20,8150010/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?

price
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    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

     

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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!