STOCK ON HAND TRACING & VALUE CALCULATION

[Nasir_Arslan35]

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). 

Any suggestions please. 

Regards,

[Anonymous]

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 ?

[daxer-almighty]

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

[dedelman_clng · Accepted solution]

Hi @Nasir_Arslan35 -

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

//Then your measure becomes
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