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 de...
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

     

Sahir_Maharaj's avatar
Sahir_Maharaj
Icon for Super User rankSuper 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