topic Re: repost: Wine Inventory in DAX Commands and Tips

repost: Wine Inventory

glennkobes — Mon, 12 Oct 2020 08:45:29 GMT

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?

Re: repost: Wine Inventory

themistoklis — Mon, 12 Oct 2020 11:42:03 GMT

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

Re: repost: Wine Inventory

glennkobes — Mon, 12 Oct 2020 18:20:41 GMT

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