No items in stock based on in - out date

[Johnrevheim]

I have a need to visualize/count the number of items in stock day by day. I have an Excel spreadsheet with date in , date out and item description. How do I visualize this in a graphs showing each day and the number of each item ?

[Anonymous · Accepted solution]

Hi @Johnrevheim,

Based on your description, you want to get the current  in-stock item, right?

If as I said, you can refer to below steps:

Tables(stock table, datetable):

 

Formula:
DateTable = CALENDAR(min(Stock[date-in]),MAX(Stock[date-out]))

Measure:

InStock Item =
var currDate=MAX([Date])
var temp= ADDCOLUMNS(Stock,"range",CONCATENATEX(CALENDAR([date-in],[date-out]),[Date]&","))
return
LEFT(CONCATENATEX(FILTER(temp,ISERROR(SEARCH(currDate,[range]))=FALSE()),[item]&","),LEN(CONCATENATEX( FILTER(temp,ISERROR(SEARCH(currDate,[range]))=FALSE()),[item]&","))-1)

Create a calculate column to display the result:

 

Notice: the result of temp table:

 

If above is not help, please provide more detail information about your requirement.

Regards,
Xiaoxin Sheng

[vangaps]

Hi @Anonymous,

for the same pattern data, could you please provide the solution , how to get the count of stock that is present in inventory right now and also solution to check the Stock present for previous months using a month year filter.

Ex: filter condition for stock

 Stock in date<=last day of the required month

                                   Stock out date(or sold date) >= first day of the next month or null. --Please let me know if this condition works for checking the stock count for previous months as well.

[Michiel]

If you're asking about the best visualization, I'd do this with a line chart, although the number of different items should not be too large.

Calculating the current stock works by calculating both the cumulative inflow and cumulative outflow. The data should start with an initial stock level to do this correctly. You can calculate the cumulative with e.g.

CumulativeIn = 
VAR EndDate = MAX(Calendar[Date])
RETURN
CALCULATE(SUM(StockTransactions[Amount]),
    FILTER(ALL(Calendar), Calendar[Date] <= EndDate)
    )

[Johnrevheim]

Sorry about the ignorance - new user. Do I add this formula as a new measure or new column?

[Greg_Deckler]

Please provide some sample data. Thanks.

[Johnrevheim]

example data

date in        date out       item 

1.1.2016     2.2.2016         A

3.1.2016    15.1.2016        B

9.1.2016    10.2.2016        A

4.2.2016    10.2.2016        B

etc.

[Greg_Deckler]

That's your only data? You don't have any inventory data or does each row represent 1 thing?

[Johnrevheim]

Hi,

yes. the data is structured like this. A is one type, B is one type etc. What I need to see on a time scale is how many of A, how many of B etc at any given date. 

Thanks for helping,