Forum Discussion

Nasir_Arslan35's avatar
Nasir_Arslan35
Regular Visitor
6 years ago
Solved

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). 
 
Any suggestions please. 
 
Regards,
@inventory @DAX
@NeedHelp
@stock_managment
  • dedelman_clng's avatar
    dedelman_clng
    6 years ago

    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

3 Replies

  • daxer-almighty's avatar
    daxer-almighty
    Icon for Solution Sage rankSolution Sage
    6 years ago

    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's avatar
    dedelman_clng
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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 ?