2 Replies Latest reply on Oct 15, 2018 2:28 AM by Patrick Weber

# stock value at the beginning and at the end for each month

Hi friends of Tableau.

My issue: I have data of a warehouse (see example workbook) in this case for just one product.

On the date column you'll see the date when the stock value has changed the last time.

Now I need to know the stock value at the beginning and at the end for each month.

In this example there would be  135 on Sept 01 because the value has changed the last time on Aug 29.

At the end of Sept (Sept 30) there would be still 89.

Which kind of calculation/function could I use?

Thanks in advantage for any hint or tip.

• ###### 1. Re: stock value at the beginning and at the end for each month

Hi Patrick,

You can try below logic:

Start date of month : DATETRUNC('month',[Date])

End date of month : DATEADD('day',-1,  MAKEDATE(YEAR([Date]), MONTH([Date])+1,1))

max date = { FIXED YEAR([Date]),MONTH([Date]):max([Date])}

min date = { FIXED YEAR([Date]),MONTH([Date]):min([Date])}

previous value = LOOKUP(sum([Stock]),-1)

Stock Value =

IF attr([min date])<>attr([Start date of month]) and attr([min date])=ATTR([Date]) THEN  [previous value]

ELSEIF attr([max date])<>attr([End date of month]) and attr([max date])=ATTR([Date]) THEN  SUM([Stock])

END

• ###### 2. Re: stock value at the beginning and at the end for each month

That's a nice solution.

How I can get a view like in this table below?

I already separated  the both if conditions of Stock Value for begin and end but then there's still null (see my attached workbook).

Example view:

Product Name
Month
begin
end
Product ABCJuly110157
August157135
September13589
October8974