topic Re: Retrieve Measures for Previous Event (not date based) in DAX Commands and Tips

Retrieve Measures for Previous Event (not date based)

xmark — Wed, 18 Sep 2019 04:29:38 GMT

Hi all, I have a model which is centred around "events" (such as a festival or sporting day), these events repeat year on year, but the dates/days shift each year, so traditional same-time-last-year measures dont really work for my client.

My event table contains a field called "PreviousEventId" which referes to the previous time the event ran (which could be last year, or could be last week!). This field is a reference to the same event table.

I have created the basic measures for fact tables (ticket sales for example) - and now I need to create the "same-time-last-event" measure - but am really stuck on how to achieve this.

Here is some data for example puposes only:

Event Table:

EventId	Description	PreviousEventId
EV112	2018 School Sports Day	(null)
EV119	2019 School Sports Day	EV112

Tickets:

SaleId	Amount	EventId
S11100	10.50	EV112
S11101	15.50	EV112
S11102	28	EV112
S11103	100	EV119
S11104	150	EV119

My desired output would be a report like so:

Event	Curent Sales Total	Sales Total Last Event
2018 School Sports Day	250	54

The above are very simplistic examples, but the real effort for me is around the measure for the "Sales Total Last Event"

I hope that makes sense.

Re: Retrieve Measures for Previous Event (not date based)

Nathaniel_C — Wed, 18 Sep 2019 20:58:31 GMT

Hi xmark

Just to clarify in the output is it 2018 or 2019 School Sports Day?

Thanks,

Nathaniel

Re: Retrieve Measures for Previous Event (not date based)

Nathaniel_C — Wed, 18 Sep 2019 22:42:11 GMT

Hi xmark ,

Re: Retrieve Measures for Previous Event (not date based)

Nathaniel_C — Wed, 18 Sep 2019 23:05:57 GMT

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

Re: Retrieve Measures for Previous Event (not date based)

xmark — Thu, 19 Sep 2019 01:02:45 GMT

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?

Re: Retrieve Measures for Previous Event (not date based)

Nathaniel_C — Fri, 20 Sep 2019 01:07:46 GMT

Hi xmark

Working on a project, but will get back to this soon. In the meantime, here is my PBIX

Nathaniel