Challenge of getting ending inventory with movements and "resets"

[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

Winelog.xlsx

 

[Matt Allington]

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

this code seems overly complex

=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

=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]

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.

Thanks for your help!

 

[GlennKobes]

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.

Winelog.xlsx

Shared with Dropbox

www.dropbox.com