wine inventory

[glennkobes]

Hello all. I have a wine inventory where I track wine that I add and take. However, periodically I just take an inventory of the wine on hand and I want to start using that number going forward.  I have my wine log joined to a calendar table. 

below is a sample of the data. 

Date	Transaction Type	Brand	Year	Type	Bottles
9/3/2017	Take	Patoinos	2016	White	5
9/6/2017	Set	Patoinos	2016	White	10
9/7/2017	Take	Patoinos	2016	White	2
9/9/2017	Add	Patoinos	2016	White	1
9/29/2020	Set	Patoinos	2016	White	7

I want to have a summary as follows

Year	ending Inventory
2017	9 (10-2+1)
2018	9 (10-2-2+1)
2019	9 (10-2-2+1)
2020	7 (7 set value in 2020)

The numbers in the parethesis are how the number would be derived. For example. In 2017 the inventory should start with the set amount on 9/6/2017, subtract 2 for the take transaction in 9/7/17 and add 1 for the add on 9/9/17.  The take on 9/3/17 is not relevant because there is a subsequent "Set" on 9/6/2017. 

I created the following measures (with the dax)

Measure	Value in 2017	DAX Code
Last Date In Context	9/9/2017	=CALCULATE( LASTDATE( 'WineLog'[date] ), FILTER( ALL( 'Calendar'[Date] ), 'Calendar'[Date] <= MAX( 'Calendar'[Date] ) ) )
Last Set Date In Context	9/6/2017	=CALCULATE([Last Date In Context],WineLog[Transaction Type]="Set")
Wine Log Set Bottles	10	=CALCULATE( SUM( WineLog[Bottles] ), WineLog[Transaction Type] = "Set", FILTER( ALL( 'Calendar'[Date] ), 'Calendar'[Date] <= MAX( 'Calendar'[Date] ) ) )
Winelog Take Bottles	-7	=SUMX( WineList, CALCULATE( sum(WineLog[Bottles])*-1, WineLog[Transaction Type] = "Take", filter(all( 'Calendar'),'Calendar'[date] >= [Last Set Date In Context] )))

Everything seems ok except for the WineLog Take Bottles. I want this value to be all the Take transactions starting from the last "Set" within the context (ie -2). Instead. The number represents all the take transactions. 

What am I doing wrong?

Link to my file 

https://www.dropbox.com/s/fm6kib663cm4j8c/winelog.xlsx?dl=0

[amitchandak]

@glennkobes , Create a year column and try like

Year = Year([Date])

calculate(lastnonblankvalue(Table[Date], sum(Table[Bottles])), allexcept(Table, Table[Year]))

or

calculate(lastnonblankvalue(Table[Date], sum(Table[Bottles])), filter(allselcted(Table), Table[Year] =max(Table[Year])))

[glennkobes]

Thanks @amitchandak!

I am using Excel power pivot and do not see lastnonblankvalue as an option. I see lastnonblank, but it seems to have a different syntax.  

[glennkobes]

Some additional information with respect to my issue.

I created a new measure with a hardcoded date.

=SUMX(
WineList,
CALCULATE(
sum(WineLog[Bottles])*-1,
WineLogtt[Transaction Type] = "Take",
filter( 'Calendar','Calendar'[date] >= date(2017,09,06 ))))

This returns "-2", which is what I expect.

When I use
=SUMX(
WineList,
CALCULATE(
sum(WineLog[Bottles])*-1,
WineLog[Transaction Type] = "Take",
filter(all( 'Calendar'),'Calendar'[date] >= [Last Set Date In Context] )))

it returns "-7"

but Last Set Date in Context returns 9/7/2017.

???? what am I missing ???

Link to file in dropbox --> https://www.dropbox.com/s/fm6kib663cm4j8c/Winelog.xlsx?dl=0