Forum Discussion
Retrieve Measures for Previous Event (not date based)
Hi xmark ,
Part of this was pretty straightforward, but ended up creating a new table with SUMMARIZE to gather the previous Sales Amount.
Current Sales Total = CALCULATE(Sum(Tickets[Amount]),Filter((Events),Events[EID] =Max(Events[EID])))
This works as we filter for the event.
Previous Event = CALCULATE(Max(Events[P_EID]),fILTER(Events,MAX(Events[EID]) = MAX(Events[EID])))
I checked this with an additional Evebt in Events.
Sales Total Last Event = CALCULATE(MAX(NewTable[Sales]),Filter(NewTable,NewTable[EventID] = [Previous Event]))
This works as we don't have undo filter, and the amount is already summed.
This the new table with a quick summary.
No relationship on the new table, so we can harvest and the filter does not affect it.
Used your example tables, with maybe an abbreviation here and there.
Let me know if you have any questions
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Hi Nathaniel_C
thanks for your reasponse, I appreacite the help! I can kind of follow what you did, but I am not sure why you did it that way...
A couple of follow-up questions:
If I have a set of other dimension tables linking from the Tickets table (such as Category1, Category2, Classification, Status, many others...) I would ideally like to use these dimensions too whenever I use the new measure for previous event sales. But if I create a new table using summarize like you have done, I am limited in what I can do, is that correct (since the summarize table only contains 2 columns, I would have to add columns to the table for each dimension I wanted to include)?
I had a go at this myself last night, but Im not sure if I am on the right track, here is my DAX:
previousData =
VAR previousSelectedEventId = SELECTEDVALUE(Event[PreviousEventId])
RETURN
CALCULATE(
SUM(Tickets[Amount]),
ALLEXCEPT(Classification, Category1, Category2, Category3),
Attendance[EventId] = previousSelectedEventId
)
This appears to work, but my concern is that since I am clearing the filter on Classification, Category1, Category2, and Category3... if my end user uses this measure on another dimension table that I have not included in the ALLEXCEPT function, it will not work as expected... is that correct?