Formula for this?

[nhewett]

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!

[technolog · Accepted solution]

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!