Forum Discussion

glennkobes's avatar
glennkobes
New Member
5 years ago

wine inventory

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 

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

 

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      glennkobes
      New Member
      5 years ago

      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's avatar
        glennkobes
        New Member
        5 years ago

        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