Forum Discussion

glennkobes's avatar
glennkobes
New Member
5 years ago
Solved

repost: Wine Inventory

Hi, This is a repost as I have not yet found a solution. Orignal post.  

I also have the link to the file (here). My main issue is that I have a measure to get the latest date for which there is a "Set" entry. [Last Set Date In Context] returns 9/6/2017, which is what I expected. However, when I use that messure to return a running total, I do not get the expected result. 

 

=SUMX(
WineList,
CALCULATE(
sum(WineLog[Bottles])*-1,
WineLog[Transaction Type] = "Take",
filter(all( 'Calendar'),'Calendar'[date] >= [Last Set Date In Context] )))

 

returns -7, which is not correct. However, if I hard code the 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.

 

Any thoughts on what I am doing wrong?

 

  • themistoklis's avatar
    themistoklis
    5 years ago

    glennkobes 

     

    Make sure you assign the date in 'Last Set Date In Context' field to a variable.

     

    Then add the variable to the measure:

     

     

    Measure =
VAR _Last_Set_Date_In_Context = MAX(WineLog[Bottles])

RETURN
SUMX(
WineList,
CALCULATE(
sum(WineLog[Bottles])*-1,
WineLogtt[Transaction Type] = "Take",
filter( 'Calendar','Calendar'[date] >= _Last_Set_Date_In_Context)))

     

     

2 Replies

  • themistoklis's avatar
    themistoklis
    Icon for Community Champion rankCommunity Champion
    5 years ago

    glennkobes 

     

    Make sure you assign the date in 'Last Set Date In Context' field to a variable.

     

    Then add the variable to the measure:

     

     

    Measure =
VAR _Last_Set_Date_In_Context = MAX(WineLog[Bottles])

RETURN
SUMX(
WineList,
CALCULATE(
sum(WineLog[Bottles])*-1,
WineLogtt[Transaction Type] = "Take",
filter( 'Calendar','Calendar'[date] >= _Last_Set_Date_In_Context)))

     

     

    • glennkobes's avatar
      glennkobes
      New Member
      5 years ago

      Thanks themistoklis! This helped alot! I also discovered that I could use the Datesbetween function to get the proper date range. Below is the formula for the measure that returned the correct value. 

       

      Thanks again for your help! 

       

      =VAR _Last_Set_Date_In_Context = [Last Set Date In Context]
      RETURN
      SUMX(
      WineList,
      CALCULATE(
      SUM( WineLog[Bottles] ) * -1,
      WineLogtt[Transaction Type] = "Take",
      DATESBETWEEN(
      'Calendar'[Date],
      _Last_Set_Date_In_Context,
      MAX( 'Calendar'[Date] )
      )
      )
      )