finding value of stock amount for each month

[Anonymous]

Hi,

I want to find stock value of products for each month. Addition to fallowing tables, I also have a product table related to both tables. Each product have different price for each month. I need to multiply cumulative stock amount with price of the product for each month. I will be appreciated for any idea about how to do that. Thanks.

stock table

product_id
price
month
year

price table

product_id

action_date

amount

[v-lionel-msft]

Hi @Anonymous ，

Are your tables like these?

If yes, you can solve your issue like this:

In 'Price table':

first, split the [action_date] column into [Year] and [month] columns;

Year = YEAR([action_date]) 
Month = MONTH([action_date])

Second, create related columns:

// In 'Price table'
cumulative stock amount for each month = 
CALCULATE(
    SUM('price table'[amount]),
    FILTER(
        'price table',
        [Month] = EARLIER([Month])
        &&
        [Year] = EARLIER([Year])
        &&
        [product_id] = EARLIER([product_id])
    )
)

//In 'stock table':
Price for each product for each month = 
CALCULATE(
    SUM([price]),
    FILTER(
        'stock table',
        [product_id] = EARLIER([product_id])
        &&
        [year] = EARLIER([year])
        &&
        [month] = EARLIER([month])
    )
) 
// Then, create another column in 'price table':
Price for each product for each month = 
LOOKUPVALUE(
    'stock table'[Price for each product for each month],
    'stock table'[product_id], 'price table'[product_id],
    'stock table'[month], 'price table'[Month],
    'stock table'[year], 'price table'[Year]
)

Third, create a measure:

stock value of products for each month = 
SUMX(
    'price table',
    [cumulative stock amount for each month] * [Price for each product for each month]
)

The result:

Is this problem solved?

If it is solved, could you kindly accept it as a solution to close the case?

if not, feel free to let me know.

Best regards,
Lionel Chen