Challenge of getting ending inventory with movements and "resets"

GlennKobes

New Member
Joined
Oct 5, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.



DateTransaction TypeBrandYearTypeBottles
9/3/2017TakePatoinos2016White5
9/6/2017SetPatoinos2016White10
9/7/2017TakePatoinos2016White2
9/9/2017AddPatoinos2016White1
9/29/2020SetPatoinos2016White7


I want to have a summary as follows



Yearending Inventory
20179 (10-2+1)
20189 (10-2-2+1)
20199 (10-2-2+1)
20207 (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)



MeasureValue in 2017DAX Code
Last Date In Context9/9/2017=CALCULATE(
LASTDATE( 'WineLog'[date] ),
FILTER(
ALL( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX( 'Calendar'[Date] )
)
)
Last Set Date In Context9/6/2017=CALCULATE([Last Date In Context],WineLog[Transaction Type]="Set")
Wine Log Set Bottles10=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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,240
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
Joined
Oct 5, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 5, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.

 

Watch MrExcel Video

Forum statistics

Threads
1,114,581
Messages
5,548,874
Members
410,881
Latest member
toonces
Top