Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Nasir_Arslan35
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). 
 
Any suggestions please. 
 
Regards,
1 ACCEPTED SOLUTION
dedelman_clng
Community Champion
Community Champion

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

View solution in original post

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 ?

daxer-almighty
Solution Sage
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
dedelman_clng
Community Champion
Community Champion

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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