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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
nhewett
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:

skudatestatussales category % sold
ca0x161101/04/2017out of stock0.56
ca0x161103/28/2017in stock10.29
ca0x161102/25/2018out of stock9.24
ca0x161103/30/2018in stock10.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
technolog
Super User
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!

View solution in original post

1 REPLY 1
technolog
Super User
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!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors