cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## STOCK ON HAND TRACING & VALUE CALCULATION

Hi BI community,

i've a data set approx. 450k lines from which i need to map the inventory history. I've used the following code for the measure and it displays accurate values.

SOH = CALCULATE(
SUM('STOCK MAPPING'[Trans QTY]),
FILTER(
ALL('STOCK MAPPING'[Order Date]),
('STOCK MAPPING'[Order Date])<=MAX('STOCK MAPPING'[Order Date])
)
)
However, for individual SKUs the stock values are only populated against the dates on which trasactions were registered. Secondly, i need to create a variable measure where stock on hand at each date between 2009 to present is multiplied with a standard unit cost (current one from a different table).

Regards,
1 ACCEPTED SOLUTION
Community Champion

Create a date table (using CALENDARAUTO() or something similar), then make a relationship between the date table and Order Date.  The DAX would then change to look like this, and you should get a value for every day:, even at the individual stock level.

``````SOH = CALCULATE(
SUM('STOCK MAPPING'[Trans QTY]),
FILTER(
ALL(DateTab[Date),
(DateTab[Date])<=MAX(DateTab[Date])
)
)``````

For the current value, you might want to create that as a calculated column on the Stock Mapping table, so that it works at all aggregation levels. Something like:

``````//Calculated Column
Current Value = LOOKUPVALUE(ValueTable[Current Value], ValueTable[SKU], 'Stock Mapping'[SKU])

CALCULATE (
SUM ( 'Stock Mapping'[Trans QTY] * 'Stock Mapping'[Current Value] )
FILTER (ALL(DateTab), DateTab[Date] <= MAX(DateTab[Date])
)``````

If this doesn't work for you, please share some sample data, model structure and/or sample pbix file for further assistance.

Hope this helps

David

3 REPLIES 3
Anonymous
Not applicable

When I filter per product and per Store it gives the last stock even it before the current date of SOH, so how can we calculate all stocks of last update even if filtered by product and retail
I tried with removefilters/ALL it works with product but did not work with retails and give me previous dates if the branch do not have date like last update ?

Solution Sage

For datetime calculations, you need to have a proper Dates table. Also, storing everything inside one big table should be avoided at all costs. There are too many good reasons for this to enlarge upon right now. Please create a good star-schema model with a datetime dimension and it'll all be much, much easier. Not to mention - much faster. And DAX will be much more readable/understandable.

When you have the Dates dimension, you can write:

``````// 'Stock Mapping' should be a hidden fact table.
// 'Dates' should be a date dimension that joins to
// [Order Date] in Stock Mapping.

SOH =
var __maxOrderDate = MAX( 'Dates'[Date] )
var __result =
CALCULATE(
SUM( 'STOCK MAPPING'[Trans QTY] ),
// This code will work if 'Dates'
// is marked as a date table in
// the model. If it's not, then
// you have to add ALL( 'Dates' )
// as the last argument of CALCULATE.
'Dates'[Date] <= __maxOrderDate
)
RETURN
__result``````
Community Champion

Create a date table (using CALENDARAUTO() or something similar), then make a relationship between the date table and Order Date.  The DAX would then change to look like this, and you should get a value for every day:, even at the individual stock level.

``````SOH = CALCULATE(
SUM('STOCK MAPPING'[Trans QTY]),
FILTER(
ALL(DateTab[Date),
(DateTab[Date])<=MAX(DateTab[Date])
)
)``````

For the current value, you might want to create that as a calculated column on the Stock Mapping table, so that it works at all aggregation levels. Something like:

``````//Calculated Column
Current Value = LOOKUPVALUE(ValueTable[Current Value], ValueTable[SKU], 'Stock Mapping'[SKU])

CALCULATE (
SUM ( 'Stock Mapping'[Trans QTY] * 'Stock Mapping'[Current Value] )
FILTER (ALL(DateTab), DateTab[Date] <= MAX(DateTab[Date])
)``````

If this doesn't work for you, please share some sample data, model structure and/or sample pbix file for further assistance.

Hope this helps

David

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors