Dax for calculating stale inventory

[Anonymous]

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!

[amitchandak]

@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

[Anonymous]

@amitchandak :  It accepted the measure.  Thanks!  But I am curious what the "+0" at the end does?  What is its function?

[Anonymous]

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.

[Anonymous]

Slow Moving Inventory Qty = SUM('Inventory Levels'[Qty On Hand],FILTER('Inventory Levels','Inventory Levels'[Slow Moving Inventory]="No"))

I get the error that too many arguments were passed to the sum function?

[Anonymous · Accepted 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]

@Anonymous :  Thanks I will try that as well!

[Anonymous]

@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]

@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?

[Anonymous]

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]

@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?

[Anonymous]

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]

@Anonymous This is the attribute column formula I used to return the correct results:

Slow Moving Inventory = VAR today = TODAY()

                                         VAR daysnosale = ABS(DATEDIFF(today,'Inventory Levels'[Date of Last Sale],DAY))

                                         VAR slowmove = IF(daysnosale>365 || 'Inventory Levels'[Date of Last Sale]=BLANK(),"Yes","No")

                                                 Return slowmove

Once I created this, I could easily calculate the desired values and get the correct responses.  Thanks for all of your help because it really pointed me in the right direction!

[Anonymous]

you nailed it. good job!