Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I need help with the following;
I have a Power BI report that is based on MRP & Stocks & Purchase Orders, looks like this MATRIX:
I need to add a Stock Level every week, it then will look like this MATRIX:
I need a DAX formula that calculates the stock levels for every week.
The calculation of this new measure [Next_Week_Stock] =
Current Stock measure is: Total Stock = CALCULATE(SUM(fact_TS_Stock[Avail_Stock]))
-
Demand measure: Total Demand = CALCULATE(SUM(fact_Total_Demand[Demand_Qty])
+
Purchase Order measure: Open POs = CALCULATE(SUM('fact_Purchase_Orders (MRP)'[Order_Qty]))
= [Next_Week_Stock]
Tables are properly related, table fact_TS_Stock does not contain any date, the other tables hold date and week fields.
Thank you for your help!
Hi 123abc,
Thank you for your help, but you keep adding filter date to the stock table, there is no date in the stock table, please see above table and relations screen dump.
Kind regards,
Eric
Thank you, but i do not have a date in the stock table ('fact_TS_Stock'), so we cannot filter on date there.
Expected result:
Only the first week (this week) is correcly calculated in both dax codes
To calculate the stock levels for every week in Power BI using DAX, you can create a new measure that calculates the difference between the total stock and the sum of total demand and open purchase orders. Here's a DAX formula to calculate the stock levels for each week:
Next_Week_Stock =
VAR CurrentWeek = MAX('DateTable'[Week]) // Get the current week
VAR TotalStock = CALCULATE(SUM(fact_TS_Stock[Avail_Stock]))
VAR TotalDemand = CALCULATE(SUM(fact_Total_Demand[Demand_Qty]), 'DateTable'[Week] = CurrentWeek)
VAR OpenPOs = CALCULATE(SUM('fact_Purchase_Orders (MRP)'[Order_Qty]), 'DateTable'[Week] = CurrentWeek)
RETURN
TotalStock - TotalDemand + OpenPOs
In this formula:
VAR CurrentWeek gets the current week from your date table. Make sure to replace 'DateTable' with the actual name of your date table.
VAR TotalStock calculates the total stock by summing the 'Avail_Stock' column from the 'fact_TS_Stock' table.
VAR TotalDemand calculates the total demand for the current week by summing the 'Demand_Qty' column from the 'fact_Total_Demand' table, filtered by the current week.
VAR OpenPOs calculates the total open purchase orders for the current week by summing the 'Order_Qty' column from the 'fact_Purchase_Orders (MRP)' table, filtered by the current week.
Finally, the RETURN statement subtracts the total demand and adds the open purchase orders to the total stock to get the 'Next_Week_Stock' measure for each week.
Make sure to adjust the table and column names as per your actual data model. This measure should be placed in your Power BI report to display the stock levels for every week based on your MRP, stocks, and purchase orders.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
Thank you for your help!
Unfortunately I am not getting the correct result, here shown the dax and matrix result:
Table and relations:
Thank you.
I apologize for any confusion. Let's adjust the DAX formula to ensure it calculates the stock levels correctly. In the updated formula, we'll make use of the FILTER function to filter the dates properly for Total Stock, Total Demand, and Open POs calculations:
Next_Week_Stock =
VAR CurrentWeek = MAX('fact_TS_Stock'[WeekEndDate])
VAR TotalStock = CALCULATE(SUM('fact_TS_Stock'[Avail_Stock]), FILTER('fact_TS_Stock', 'fact_TS_Stock'[WeekEndDate] = CurrentWeek))
VAR TotalDemand = CALCULATE(SUM('fact_Total_Demand'[Demand_Qty]), FILTER('fact_Total_Demand', 'fact_Total_Demand'[WeekEndDate] = CurrentWeek))
VAR OpenPOs = CALCULATE(SUM('fact_Purchase_Orders (MRP)'[Order_Qty]), FILTER('fact_Purchase_Orders (MRP)', 'fact_Purchase_Orders (MRP)'[WeekEndDate] = CurrentWeek))
RETURN TotalStock - TotalDemand + OpenPOs
This updated formula uses the FILTER function to ensure that each calculation is filtered for the correct week based on the 'WeekEndDate' field in each of the three tables. Please replace the table and field names with the actual names from your Power BI data model.
If you are still not getting the expected results, please ensure that your date fields are properly related and the data in your tables aligns with the week you want to calculate the stock for. Additionally, double-check the format and values in your 'WeekEndDate' field to make sure they match the week you are targeting.....
Explain your poblem in detail .. your screen shots in not proper visible so copy paste in text your DAX or issues.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
8 |
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
10 |