# Challenge of getting ending inventory with movements and "resets"

#### GlennKobes

##### New Member
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?

Winelog.xlsx

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### Matt Allington

##### MrExcel MVP
It’s hard without sample data, but here is what I see.

this code seems overly complex
VBA Code:
``````=SUMX(
WineList,
CALCULATE(
sum(WineLog[Bottles])*-1,
WineLog[Transaction Type] = "Take",
filter(all( 'Calendar'),'Calendar'[date] >= [Last Set Date In Context] )))``````

i think this would work
Code:
``````=CALCULATE(
sum(WineLog[Bottles])*-1,
WineLog[Transaction Type] = "Take",
filter(all( 'Calendar'),'Calendar'[date] >= [Last Set Date In Context] ))``````

but it may depend on if you have a dimension table for [transaction type]. It doesn’t look like you have. Try creating a dim table, use that in your visual instead of WineLog[Transaction Type] and then change it in the measure too.

#### GlennKobes

##### New Member
Good suggestion on creating the transaction type dimension table. I did that, but still am getting the same results. I have my file in dropbox. ( Winelog.xlsx )

what is confusing to me is that the [Last Set Date In Context] measure returns the expected date, but it does not seem to work when I use it in the Wine Log Take measure. I am guessing I have something wrong with the filter context or the table relationships, but I cannot figure it out.

#### GlennKobes

##### New Member
Hi, some more information. 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 ???

Here is a link to the file.

Replies
1
Views
65
Replies
2
Views
125
Replies
0
Views
47
Replies
3
Views
647
Replies
6
Views
2K