This is a list of products, I am looking to for advice on a dax formula that will check the following columns, QOH >0, Last Sale Date 4 years before TODAY(), Last Purchase Date 4 years before Today(). Then i want to multiple the QOH of the qualifying part numbers x average cost to get a dollar value of dead inventory.
I wonder if calculate table would be the best way to go? also how do you formulize today-4 years so the table will keep catching new numbers
Be nice to see a Video from Sam on some Inventory Management DAX.
Greg
I did supply the PBIX File did it not come through? Also, QOH is quantity on hand. Here they Are again Data Model.xlsx (372.2 KB) Data Model Testing.pbix (23.1 MB)
I took the freedom for a different approach, which is based upon the āItem Branch Recordā-table.
PBIX attached: Data Model Obsolete Stock.pbix (23.1 MB)
The request is calculating the value of non-sold and non-purchased stock elder then four years, this on a daily basis.
The chosen workout is as follows:
Calculate the value of all stock at cost price
QOH at Cost price TOTAL = SUMX( āItem Branch Recordā, āItem Branch Recordā[Last Cost] * āItem Branch Recordā[QOH] )
Calculate the value of stock purchased and sold more or equal of 4 years ago , whereby I made the period dynamic with a slicer in quarters ( range from 4 to 20, from 1 to 5 years backwards)
QOH at Cost price BEFORE inactive period =
Var InActQ = [Inactive-Quarters]
Var InActYrs = ROUNDDOWN( InActQ /4, 0)
Var InActMnth = InActQ - InActYrs * 4
VAR InactivePeriod = DATE( YEAR(TODAY()) - InActYrs, MONTH(TODAY() - InActMnth), DAY(TODAY()))
Var QOHbefore = CALCULATE(
[QOH at Cost price TOTAL],
'Item Branch Record'[QOH] > 0,
'Item Branch Record'[Last Sale Date] < Inactiveperiod,
'Item Branch Record'[Last Puchase Date] < InactivePeriod )
RETURN QOHbefore`
Calculate the value of stock sold/purchased till 4 years ago, which should be excluded, by measure branching.
QOH at Cost price AFTER inactive period = [QOH at Cost price TOTAL] - [QOH at Cost price BEFORE inactive period]
The what I call āobsolete stockā can be calculated only as when the total stock value is equal to the old stock (nothing sold in newer periods)
Obsolete Stock (lines, non total) = IF( [QOH at Cost price TOTAL] = [QOH at Cost price BEFORE inactive period], [QOH at Cost price BEFORE inactive period],0)
This is a little more complicated than I need. i added a calculated column that gave me Current Inventory Value = QOH * Avg Cost. Then using Jaimeās Variable FourYearsAgo. i wrote the following formula
Dead Inventory $ =
Var FourYearsAgo = DATE(year(TODAY())-4,MONTH(TODAY()),day(TODAY()))
return
Dead Inventory $ =
Var FourYearsAgo = DATE(year(TODAY())-4,MONTH(TODAY()),day(TODAY()))
The formula works fine, it gives you the inventory value of stock elder then 4 years (, regardless of it is selling/purchased nowadays).
I focused also on the part of your question, that the last sales and purchase of a particular product-item has to be at least 4 years backwards, that is the part not solved with your formula. (to clean up the product line of product not sold for 4 years)
Probably I misinterpret your question, rereading your question:
Hopefully it answers your question and obviously you decide what information you want.
The Variable Jamie created should take care of that.
VAR FourYearsAgo = DATE( YEAR(TODAY()) - 4, MONTH(TODAY()), DAY(TODAY()))
I encountered a problem though, we have a couple different locations. some of these branches are listed as numbers and some as text. I tried to add a a filter to the calculation to only select items in Branch 1 but power bi didnāt like that. The Branch Number Column is currently formatted as text
As far as I know, you can not use very much filter-conditions in one DAX-formula, and it reduce performance. With Jamies solution you have already 3 filters in place.
Generally, if you have not much locations, you could choose for a slicer with a list of the locations.
If you want to select locations through numbering, you could add a Lookup table with the locations, adding a location-number column. ((Much) less favorable is adding a calculated column in your fact table with multiple conditions for numbering).
I think i am going to have to use the slicer as well, as there different dates and QOH for each number at each branch. or maybe do a measure for each branch
