Calculating Inventory value for the stocks on hand on any selected date including historic values

[Anonymous]

Hi all,

I am trying to create a measure in DAX to calculate the inventory value for the items currently in stock and also historically based on the date selected by the user.

I have attached the pbix file with sample data and a simplified data model of StockAudit table and a date dimension linked to it In my data I have:

• New Part number & New SL (Key combination)
• Time Stamp (Date of the stock activity)
• New Unit Cost (Price)
• Old Qty On hand
• New Qty On hand ( if its 0 out of stock, if its 1 or more then in stock)
  
  

There are some other columns and dimensions but the above should provide all detail required.

Expected result:

Now I need to write a DAX statement for calculating the inventory value (Price) for the items currently in stock for any given date:
(Example of my sample data)

1. If the date is selected as 22.12.2021 the result set is the sum of new unit cost (53.500) with highest timestamp (in this case 20.12.2021) for three stocklines (144,145,150) with the condition new_oty_oh=1 three rows should be retrived
   
2.   If the date is selected as 20.02.2022 the result set is the sum of new unit cost(35.000) with highest timestamp (in this case 18.02.2022) for only two stocklines (144,145) since 150 is out of stock with new_qty=0, onyl two rows should be retrived.
   (There are repetitive records for some stocks because of different transactions to the stock on same day)

What Measures or Calculated columns would I need to achieve this output since the expectation is to check through each and every record for a New Part number & New SL combination to find the inventory value efficiently. 

Link to the pbix file with data : https://1drv.ms/u/s!AvA391_m0W9IpDsf36b_96OIineq?e=yxuboq

Looking forward to the response, Thanks!

[Anonymous]

Hi @Anonymous Thanks for the response.

My requirement is not completly fulfilled though.

I have a below example where I need total Inventory cost for the max of timestamp:

In this case when I select today's date 24th May 2022 for this part number and stock line I need the final row with inventory cost as 28,617,43, i get last row for max timestamp but the unit cost is sum of all lines.

Please find my DAX as of below:

Cost value =

SUMX('StockAuditSample',('StockAuditSample'[New Qty On Hand]*'StockAuditSample'[New Unit Cost]))

Inventory value latest =

VAR selecteddate=MAX('Date'[Date])

VAR temptableA=SUMMARIZE(FILTER(ALLSELECTED('StockAuditSample'),'StockAuditSample'[Time Stamp]<=selecteddate && MAX('StockAuditSample'[Time Stamp])=StockAuditSample[Max timestamp]),'StockAuditSample'[PK_Index],"Cost",CALCULATE('StockAuditSample'[Cost value]))

RETURN

CALCULATE('StockAuditSample'[Cost value],FILTER(temptableA,LOOKUPVALUE('StockAuditSample'[PK_Index],'StockAuditSample'[PK_Index],[PK_Index])))

Could you please suggest where I'm wrong in this to get just the unit cost of the particular record, thanks.

[Anonymous]

Hi @Anonymous ,

As far as I know [Time Stamp] column is a datetime format column and show in date type. I suggest you to create a date only  [Time Stamp] column first.

Time Stamp (Only Date) = DATEVALUE(StockAuditSample[Time Stamp])

Then I suggest you to create a measure to filter your visual.

Measure = 
VAR _Highest_Timestamp = CALCULATE(MAX(StockAuditSample[Time Stamp (Only Date)]),FILTER(ALL(StockAuditSample),CALCULATE(SUM(StockAuditSample[New Qty On Hand]))<>0&& StockAuditSample[Time Stamp (Only Date)]<=SELECTEDVALUE('Date'[Date])))
VAR _new_oty_oh = CALCULATE(SUM(StockAuditSample[New Qty On Hand]))
RETURN
IF( MAX(StockAuditSample[Time Stamp (Only Date)]) =_Highest_Timestamp,IF(_new_oty_oh<>0,1,0))

Add this measure into visual level filter and then set it to show items when value = 1.

Select 2021/12/22:

Select 2022/02/20:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.