repost: Wine Inventory

[glennkobes]

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 · Accepted solution]

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

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