Forum Discussion
Inventory Trend Analysis
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.
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
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.
3 Replies
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.
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?
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.
