cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

## Formula for this?

Hey there!

I'm trying to deleveop a measure or column but am having difficulties figuring out how to approach the forumla. Basically, I'm trying to calculate the total % that something was out of stock.

Context:

In it's own table, our system records an entry every time something has a stock status change. The data is recoreded like this:

 sku date status sales category % sold ca0x1611 01/04/2017 out of stock 0.56 ca0x1611 03/28/2017 in stock 10.29 ca0x1611 02/25/2018 out of stock 9.24 ca0x1611 03/30/2018 in stock 10.02

To determine the total % of the sales curve that the item missed, I need to calculate the difference between the logged values. As an example, if I was trying to identify the out of stock % of the above item during the year of 2018, I'd take 10.29-0.56= 9.73%.

How would I structure my formula to calculate something like that? Thanks for the help!

1 ACCEPTED SOLUTION
Super User

Alright, let's break this down. What you're trying to do is calculate the difference in the "% sold" value between the "out of stock" date and the subsequent "in stock" date for a given SKU and year.

To achieve this, you can create a measure in Power BI using DAX. Here's a way to approach this:

First, you'd need to get the "% sold" value for the "out of stock" date. You can use the LOOKUPVALUE function for this. Then, you'd need to get the "% sold" value for the subsequent "in stock" date. Again, LOOKUPVALUE can be used, but this time you'd filter for the next "in stock" date that's greater than the "out of stock" date.

Once you have both values, you simply subtract the "out of stock" value from the "in stock" value to get the difference.

Here's a DAX measure that should help:

OutOfStockPercentage =
VAR CurrentSKU = MAX('TableName'[sku])
VAR CurrentDate = MAX('TableName'[date])
VAR OutOfStockValue = LOOKUPVALUE('TableName'[% sold], 'TableName'[sku], CurrentSKU, 'TableName'[date], CurrentDate, 'TableName'[status], "out of stock")
VAR NextInStockDate = MINX(FILTER('TableName', 'TableName'[sku] = CurrentSKU && 'TableName'[date] > CurrentDate && 'TableName'[status] = "in stock"), 'TableName'[date])
VAR InStockValue = LOOKUPVALUE('TableName'[% sold], 'TableName'[sku], CurrentSKU, 'TableName'[date], NextInStockDate)
RETURN IF(ISBLANK(OutOfStockValue) || ISBLANK(InStockValue), BLANK(), InStockValue - OutOfStockValue)
This measure first determines the current SKU and date context. It then looks up the "% sold" value for the "out of stock" date and finds the next "in stock" date and its corresponding "% sold" value. Finally, it calculates the difference between the two values. If either value is missing, it returns a blank.

You can then use this measure in your report to get the out of stock percentage for each SKU and date combination. Remember to replace 'TableName' with the actual name of your table in Power BI. Hope this helps!

Super User

Alright, let's break this down. What you're trying to do is calculate the difference in the "% sold" value between the "out of stock" date and the subsequent "in stock" date for a given SKU and year.

To achieve this, you can create a measure in Power BI using DAX. Here's a way to approach this:

First, you'd need to get the "% sold" value for the "out of stock" date. You can use the LOOKUPVALUE function for this. Then, you'd need to get the "% sold" value for the subsequent "in stock" date. Again, LOOKUPVALUE can be used, but this time you'd filter for the next "in stock" date that's greater than the "out of stock" date.

Once you have both values, you simply subtract the "out of stock" value from the "in stock" value to get the difference.

Here's a DAX measure that should help:

OutOfStockPercentage =
VAR CurrentSKU = MAX('TableName'[sku])
VAR CurrentDate = MAX('TableName'[date])
VAR OutOfStockValue = LOOKUPVALUE('TableName'[% sold], 'TableName'[sku], CurrentSKU, 'TableName'[date], CurrentDate, 'TableName'[status], "out of stock")
VAR NextInStockDate = MINX(FILTER('TableName', 'TableName'[sku] = CurrentSKU && 'TableName'[date] > CurrentDate && 'TableName'[status] = "in stock"), 'TableName'[date])
VAR InStockValue = LOOKUPVALUE('TableName'[% sold], 'TableName'[sku], CurrentSKU, 'TableName'[date], NextInStockDate)
RETURN IF(ISBLANK(OutOfStockValue) || ISBLANK(InStockValue), BLANK(), InStockValue - OutOfStockValue)
This measure first determines the current SKU and date context. It then looks up the "% sold" value for the "out of stock" date and finds the next "in stock" date and its corresponding "% sold" value. Finally, it calculates the difference between the two values. If either value is missing, it returns a blank.

You can then use this measure in your report to get the out of stock percentage for each SKU and date combination. Remember to replace 'TableName' with the actual name of your table in Power BI. Hope this helps!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors