Event Year YoY Comparison

[Wolfmeister]

We have a scenario where an event (e.g. London Marathon) takes places in a given year, referred to as Event Year.  Donations can be made to this event however in other calendar years - so lets say for an event in 2018, donations can be made to the event in 2017, 2018 and 2019, i.e. before and after the event.

I need to do a YoY comparison by event year, which I am attempting to do in DAX.  So let's say I want to compare the 2018 event to the 2019 event, where the donations for 2018 event will range from Apr 2017 to Mar 2019, and for 2019 event will range from Jul 2018 to Sep 2019.  

I need the donations made in the 2018 event year (Apr 17 - Mar 19) to be displayed against the 2019 event year, and donations made in 2019 event year (Jul 18 - Sep 19) to be displayed against the 2020 event year. 

In Excel it would be a SUMIF:

Event Year 2020=SUMIF(aggregated!$A$2:$A$895,"2019",aggregated!$D$2:$D$895)

I'm fairly new to DAX  and so any help on how to accomplish this would be appreciated.  I'm sure this involves the EARLIER fuction somehow!

Thanks

[Anonymous]

Say you have a table called Events:

EventID	Event Name	Event Year
1	Marathon	2018
2	Race	2018
3	Focking Marathon	2018
4	Marathon	2019
5	Race	2019
6	Focking Marathon	2019

One thing to note: EventID is unique, Event Name is unique within Event Year and the same across Event Years, Event Year is... well, the event's year.

Now, you have another table that stores Donations:

EventID	Donation Date	Donation Amount
1	2017-01-01	1
1	2019-01-01	2
1	2020-01-01	-3
2	2017-01-01	1
2	2020-01-01	-1
3	2016-01-01	1
...	...	...

You get the idea. Connect the tables on EventID. Filtering is 1:* from Events to Donations.

Now, you create a measure

[Total Donations] = SUM( Donations[Donation Amount] )

To show donations for the same event but from the year before you do:

[Donations PY] =
var __currentYear = selectedvalue( Events[Event Year] )
return
	calculate(
		[Total Donations],
		Events[Event Year] = __currentYear - 1
	)

If you select several Event Names for one year, this will show you the sum of donations for the same events in the prior year. If you select events from several years, it'll show BLANK, as it makes no sense to show PY donations for events from different years at the same time.

Bear in mind that Donations is a fact table and as such should have all columns hidden and in Events only Event Name and Even Year may be exposed.

Best

D

[AlexAlberga727]

@Wolfmeister 

I handle YoY comparisons within the eCom world, and imagine you can handle "Events" similiar to how I handle "Accounts" when reviewing performaces YoY.

Ensure you're categorizing your records by "Event" - and Name the Events accordingly.

Data Example - 

Record 1 [Date, Event(A), Donation Made, CriteriaX, CriteriaY, CriteriaZ]

Record 2 [Date, Event(B), Donation Made, CriteriaX, CriteriaY, CriteriaZ]

Record 3 [Date, Event(A), Donation Made, CriteriaX, CriteriaY, CriteriaZ]

ect..

As long as you have a field specifying the Date, Event Name, and Donations($) - Then you can create the following measures:

Total Donations = 
SUM ( [Donations] )

Total Donations LY =
CALCULATE ( [Total Donations], DATEADD ( DATETABLE[DATES], -1, YEAR ) 

These two measures will allow you to review Total Donations (Overall, or with Sliced/Filtered Data), and the Total Donations LY for the same data.

Create a report containing a Slicer by Year, and a Table with the values of Events, Total Donations, and Dontations LY.

Let me know if this helps, enjoy!

[Wolfmeister]

Thanks @AlexAlberga727 , appreciate the feedback!