Inventory Trend Analysis

[JamesGordon]

Hi all,

I’m sure there is a simple way to achieve this but I am struggling to achieve what I am after. I am trying to create bar chart that shows the amount of stock on hand today and historically. I’m trying to analyse stock trends such as current stock level vs historical stock levels.

https://jamesgordon-my.sharepoint.com/:x:/g/personal/james_moss_jgordon_co_uk/EXVDaTiYG2ROoNk4gec8Fi... 

In my data (Sample file link above) I have:

• Rcv date – Date Received in stock
• Inv date – Date Sold out of stock
• If Inv date if blank means still in stock
• Price

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

I also have a separate date table/calendar.

What Measures or Calculated columns would I required to display a bar graph by date as per below (the graph is wrong but gives an idea what im looking to achieve)?

Any help would be appreaicted

[johnt75]

It sounds like there may be a filter being based from the Date table. Is there a relationship between the Date table and the data table? If there is you could add REMOVEFILTERS('Date') to the CALCULATE statement.

[johnt75 · Accepted solution]

The basic formula is

Stock Level = 
var maxDate = MAX('Date'[Date])
var minDate = MIN('Date'[Date])
return CALCULATE( [Base Measure], 'Table'[Rcv Date] >= minDate && 'Table'[Rcv Date] <= maxDate &&
    ( 'Table'[Inv Date] > maxDate || ISBLANK('Table'[Inv Date] )
)

You don't need any relationships between your date table and data table for this to work.

[JamesGordon]

@johnt75 

Thank you for your help. I have tried your formula and it appears to be getting close. Something isnt quite working through. 

If i put the results into a table i get the figure i would expect 10,980,463.41 for todays stock level - however when i try to put into a bar graph the 2022 stock level is only showing 4,302,876.35

I cannot see why this would be the case? as per below?