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
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!
Solved! Go to 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!
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!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
15 | |
14 | |
14 | |
9 |