The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all, newbie in Power Bi and seeking help.
I'm trying to create a formula in DAX or perhaps M to calculate the corresponding stock cover with the future sales plan.
The answer that I'm looking for in this case is 3.2 months stock cover for a stock of 60, any idea how do I get this in Power BI?
thanks!
Solved! Go to Solution.
Hi @Wcys02 ,
Please try to create a measure with below dax formual and add a card visual with this measure:
Measure =
VAR _a = 60
VAR _b = SUMX ( ALL ( 'Table' ), [Sales] )
RETURN DIVIDE ( _b, _a )
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Wcys02 ,
Please try to create a measure with below dax formual and add a card visual with this measure:
Measure =
VAR _a = 60
VAR _b = SUMX ( ALL ( 'Table' ), [Sales] )
RETURN DIVIDE ( _b, _a )
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
To calculate the stock cover in months in Power BI, you need to consider the stock level and the future sales plan. Stock cover is usually calculated as the ratio of current stock on hand to the average monthly sales. In your case, you want to calculate stock cover for a stock of 60, and you have a future sales plan. Let's assume that the future sales plan is represented in a separate column or measure in your dataset.
You can create a DAX measure to calculate the stock cover as follows:
Stock Cover (Months) =
DIVIDE(
[Current Stock],
AVERAGEX(
ALLSELECTED('Date'), -- Replace 'Date' with your date column
[Future Sales Plan]
),
0
)
Here's what this measure does:
DIVIDE is used to divide the current stock by the average monthly sales. It handles cases where the average monthly sales are zero to avoid division by zero errors.
[Current Stock] should be replaced with the appropriate measure or column that represents the current stock on hand.
ALLSELECTED('Date') is used to make sure that the average is calculated over the selected date range. Replace 'Date' with your actual date column.
[Future Sales Plan] should be replaced with the appropriate measure or column that represents the future sales plan.
Once you create this measure, you can use it in your Power BI report to display the stock cover in months. Make sure you have the proper date filtering in your visuals for it to work correctly.
By using this measure, you should be able to calculate the stock cover in months for a stock of 60, or any other stock level, based on your future sales plan.
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.
This seems to average out the monthly sales and does not give a particular stock months cover that I'm looking for.