Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Hi @xmark
Just to clarify in the output is it 2018 or 2019 School Sports Day?
Thanks,
Nathaniel
Proud to be a Super User!
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
Proud to be a Super User!
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |