cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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 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?

1 ACCEPTED SOLUTION
Super User

Hello @IgorAM,

``````Calculate Price =
VAR TotalAmountNeeded = 5000
VAR CumulativeTable =
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``````

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
2 REPLIES 2
Super User

Hello @IgorAM,

``````Calculate Price =
VAR TotalAmountNeeded = 5000
VAR CumulativeTable =
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``````

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning
Helper I

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors