Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Wcys02
Frequent Visitor

Stock Cover in Months calculation in DAX

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?

Wcys02_0-1698238403905.png

Wcys02_1-1698238678663.png

 

thanks!

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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 )

 

vbinbinyumsft_0-1698723652889.png

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.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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 )

 

vbinbinyumsft_0-1698723652889.png

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.

123abc
Community Champion
Community Champion

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:

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

  2. [Current Stock] should be replaced with the appropriate measure or column that represents the current stock on hand.

  3. ALLSELECTED('Date') is used to make sure that the average is calculated over the selected date range. Replace 'Date' with your actual date column.

  4. [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.

Wcys02
Frequent Visitor

This seems to average out the monthly sales and does not give a particular stock months cover that I'm looking for. 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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