cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors