Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am looking for a DAX formula that will return the value of inventory that has not had any sales in the last 365 days. I have a measure for the inventory value, and I have a column with the date of last sale. I also have a linked separate date table.
What I need returned is the value and quantity of only those items that have not been sold in the last 365 days.
Thanks!
Solved! Go to Solution.
Rookie mistake by me. You need to wrap your SUM function in calculate.
Slow Moving Inventory Qty =
CALCULATE (
SUM ( 'Inventory Levels'[Qty On Hand] ),
FILTER ( 'Inventory Levels', 'Inventory Levels'[Slow Moving Inventory] = "No" )
)
@Anonymous , Create this formula
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))+0
Or
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))+0
Then filter for 0 and blank
if needed enable this option on item - Show item without data
@amitchandak : It accepted the measure. Thanks! But I am curious what the "+0" at the end does? What is its function?
So there are two ways that come to mind to do this.
1) Add an attribute to your date table called "In the last 365 days" and return Yes if the date is in the last 365 days. The DAX would be something like IF( date[date]+365 > TODAY(),"Yes","No"). Then you relate your date table to your "date of last sale" in your item table. Then you can expose the inventory measure directly and filter it however you see fit to get the inventory value of stale inventory.
2) You can add this attribute directly to the inventory item table instead. i.e. IF( iventoryitem[date of last sale]+365 > TODAY(),"Yes","No"). You can then create a measure i.e. SUM(inventoryitem[sales], FILTER(inventoryitem, inventoryitem[saleslast365days] = "No") and reference that directly.
Happy to help with more specific if that doesn't work.
Rookie mistake by me. You need to wrap your SUM function in calculate.
Slow Moving Inventory Qty =
CALCULATE (
SUM ( 'Inventory Levels'[Qty On Hand] ),
FILTER ( 'Inventory Levels', 'Inventory Levels'[Slow Moving Inventory] = "No" )
)
@Anonymous : Thanks I will try that as well!
@Anonymous : the advantage of what @amitchandak posted is that if you apply a filter to your end date, the sum of sales activity will dynamically update to the trailing 12 month period based on a dynamic end date vs. my solution which is fixed on TODAY(). If you are using this measure with finance folks, I would recommend NOT having it be dynamic as they will mess with the filters give a wrong result, but YMMV.
@Anonymous : I dont need to have it locked down to specific financial periods so I think this will work. Basically, my understanding is that anytime I use your method in a visual it will look back to see if the date of last sale is anytime greateer than 365 days ago. Question is though, does the attribute column answering yes or no refresh every time with new answers?
Its a good question. I believe that the DAX column is dynamic within powerBI - meaning it calculates every time it is interacted with. This understanding is confirmed here - https://www.blue-granite.com/blog/understanding-the-differences-between-calculated-columns-measures-...
"With calculated columns, data in the column is stored in the xVelocity in-memory database, meaning that a calculation is made before the model is ever queried by the user."
@Anonymous @amitchandak : Gentlemen, I have tried both methods and when I pull the results into a matrix I and bring in the date of last sale, I am getting items that HAVE been sold within the last 12 months. What am I doing wrong?
Post the two DAX formulas for...
1) Calculating if an Item is active or not (on the inventory item table)
2) Summing the amount of sales in the last year for a specific inventory item
It should be very obvious once you do that 🙂
@Anonymous This is the attribute column formula I used to return the correct results:
you nailed it. good job!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |