Forum Discussion

xmark's avatar
xmark
Helper I
6 years ago

Retrieve Measures for Previous Event (not date based)

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:

EventIdDescriptionPreviousEventId
EV1122018 School Sports Day(null)
EV1192019 School Sports DayEV112

 

Tickets:

 

SaleIdAmountEventId
S1110010.50EV112
S1110115.50EV112
S1110228EV112
S11103100EV119
S11104150EV119

 

My desired output would be a report like so:

EventCurent Sales TotalSales Total Last Event
2018 School Sports Day25054

 

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.

 

DAX Query

5 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi xmark 

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

    Thanks,

    Nathaniel

      • Nathaniel_C's avatar
        Nathaniel_C
        Community Champion
        6 years ago

        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