Stock date history from one table

[Twin18]

Hello,

I need to make an report concering article stock history.

The problem is that there is only 1 table, and this table contains only mutations per article per date.

So i need to figure out how to pull historic data by searching in the same table multiple times.

For now i have figured out how to find the last month date containing stock data, but i can't seem to find a solution to pull the storage data value from that date.

i've made an datatable with autocalendar() to get a complete date range, but as you can see not everyday has an stock record.

Data source preview (articlenumber is not shown).

The last stock data is the following measure 

Last stock date prev. month = CALCULATE(MAX(Stockdata[Bookdate]);PREVIOUSMONTH(DateTable[Date]))

So how can i use this date to make an new measure to show the stock on that exact date? 

Regards.

[v-danhe-msft]

Hi @Twin18,

Based on my test, you could use below formula to get the previous month stock value:

Last stock date prev. month = CALCULATE(MAX('Table1'[Stock]),PREVIOUSMONTH(Calender[Date]))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Twin18]

Thanks for you're response.

I think this is the right way, the only problem now is to only get the stock for the right article (every article has his own stock records.

[v-danhe-msft]

Hi @Twin18,

It's pleasant that your problem has been solved, could you please mark the reply as Answered?

Regards,

Daniel He

[Thim]

Does it only contant 1 stock?

Could you use a lookupvalue, using the last stock date prev. month?

Something like this.

stock value prev. month = LOOKUPVALUE(Stockdata[Stock];Stockdata[Date];DateTable[last stock date prev. month])

[Twin18]

It contains stock from multiple articles, so when i create that measure i get the 'a table with multiple values was supplied..' error.