Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
@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])))
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.
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
User | Count |
---|---|
53 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
80 | |
57 | |
40 | |
19 | |
10 |