October 17, 2017 at 8:13 am
Scenario: Take a fundraising event (e.g. Virgin London Marathon), which has an event start date, say in 2017. This event receives donations. The donations can be made in the year before, the year of the event or even the year after.
I need to report the total donations received for that event by event year, not the year the donations were made. This needs to be available from a cube based on an SSAS tabular model. This in itself is not a problem - I have a fact containing the donation amounts, and an event dimension with the event year. so selecting VLM for event year 2017 aggregates all donations made to VLM 2017 correctly.
The problem I'm having is how to report on the previous event year when selecting the current event year. So what I want to see when selecting VLM 2017 by event year is one column showing the amount donated in 2017 (regardless of when the donations occurred), and another column containing the total donation amount for the previous event year, i.e. VLM 2016, again, regardless of when the donations occurred.
I think a new fact may be needed for this - FactEvent, and a measure added to this fact, however I'm struggling to build a DAX formula to calculate the previous event year amount.
Any help or advice would be appreciated.
October 17, 2017 at 8:28 am
It seems to me like this can be modeled with two dates - event date and donation date. Then the YoY calculation can be done in a normal fashion on date you want to use which seems like event date. If the event date only has the year, YoY can be coded manually. Will this work, or am I missing something?
October 17, 2017 at 8:48 am
Thanks Brian for your reply.
That sounds feasible, however the problem I'm having is with the coding of the YoY measure itself in the tabular model as I'm not well versed in DAX. Basically what I'm trying to do is to group both time frames (i.e. all donations to the 2016 + 2017 events) by event year.
The event date and the event year do both exist in the Event dimension, and there is a relationship between FactDonation and DimEvent.
October 17, 2017 at 9:22 am
Normally, I have the date in the fact table so I don't have any specific examples I can post. I don't think that it should be a problem that the date is in a dimension. It is possible, but I can't think of any reason off the top of my head. Just tie your date dimension to the event date. Anything I have would follow the patterns found here: https://www.daxpatterns.com/time-patterns/. You don't need to read everything. The earlier part explains what's going on and then once you understand enough, you can jump to the YoY calculations. Without understanding how you're modifying the filter context though, it will be difficult to make the adjustments for it being in a dimension instead of the fact table.
Hope this helps. DAX is not an easy language. It's really cool once you get the hang of it though.
October 17, 2017 at 9:29 am
Also, the example for [PY Sales] is more difficult that needed in most scenarios. I use this instead:
[PY Sales] :=
SUMX (
VALUES ( 'Date'[YearMonthNumber] ),
CALCULATE (
[Sales],
ALL ( 'Date' ),
FILTER (
ALL ( 'Date'[YearMonthNumber] ),
'Date'[YearMonthNumber]
= EARLIER ( 'Date'[YearMonthNumber] ) - 12
)
)
)
)
October 17, 2017 at 9:39 am
Brian Carlson - Tuesday, October 17, 2017 9:22 AMNormally, I have the date in the fact table so I don't have any specific examples I can post. I don't think that it should be a problem that the date is in a dimension. It is possible, but I can't think of any reason off the top of my head. Just tie your date dimension to the event date. Anything I have would follow the patterns found here: https://www.daxpatterns.com/time-patterns/. You don't need to read everything. The earlier part explains what's going on and then once you understand enough, you can jump to the YoY calculations. Without understanding how you're modifying the filter context though, it will be difficult to make the adjustments for it being in a dimension instead of the fact table.Hope this helps. DAX is not an easy language. It's really cool once you get the hang of it though.
I have created a new fact in the model called FactEvent, which has EventDateKey, and the date dimension is now tied to this. If you could provide an example based on this I would really appreciate it. Thank you for your help.
October 17, 2017 at 9:57 am
Here's some code I took from my current cube. I made a few changes to fit what I think you're looking for. I am rolling up the measure to year first so partial years don't need to be accounted for. You may end up hiding the first two measures or maybe not. Depends on what you need. Also, the "IF ( HASONEVALUE ( EventDate[YearKey] )" is to prevent it from rolling up past the year. If you don't want to worry about that, just use the divide and leave that out. Personally, it bugs me to see the grand totals when they are not relevant.
Donation Amount CY:=
SUMX (
VALUES ( EventDate[YearKey] ),
CALCULATE (
[Donation Amount],
ALL ( EventDate ),
FILTER (
ALL ( EventDate[YearKey] ),
EventDate[YearKey]
= EARLIER ( EventDate[YearKey] )
)
)
)
Donation Amount PY:=
SUMX (
VALUES ( EventDate[YearKey] ),
CALCULATE (
[Donation Amount],
ALL ( EventDate ),
FILTER (
ALL ( EventDate[YearKey] ),
EventDate[YearKey]
= EARLIER ( EventDate[YearKey] ) - 1
)
)
)
Donation Amount YoY%:=
IF (
HASONEVALUE ( EventDate[YearKey] ),
DIVIDE (
[Donation Amount CY] - [Donation Amount PY],
[Donation Amount PY]
),
BLANK ()
)
October 17, 2017 at 9:59 am
Also, the base measure:
Donation Amount := SUM(FactEvent[DonationAmount])
October 17, 2017 at 10:25 am
Brian Carlson - Tuesday, October 17, 2017 9:59 AMAlso, the base measure:
Donation Amount := SUM(FactEvent[DonationAmount])
Thanks so much for this.
In the new fact FactEvent, this is at the grain of the event, and so I have rolled up all of the donations for a particular event into a measure called DonationAmountByEventYear, which presumably would be the 'Donation Amount' base measure above? This fact also has a key, EventDateKey, which has a relationship with the date dimension.
Could I just clarify what you are referring to when are referring to EventDate and YearKey in the code above - , is EventDate referring to the date in this relationship? Also, there is no key for the year of the event in FactEvent, is this something I need to include? Or could I just go out to DimEvent to get this, which does have the event year?
Thanks
October 17, 2017 at 10:32 am
Yes to the donation amount question.
EventDate is the date dimension. I changed the name to be clear, since you can have more than one date dimension.
October 17, 2017 at 11:07 am
Brian Carlson - Tuesday, October 17, 2017 10:32 AMYes to the donation amount question.EventDate is the date dimension. I changed the name to be clear, since you can have more than one date dimension.
That seems to have done the trick!
Donation Amount PY:=
SUMX (
VALUES ('Event'[Event Year]),
CALCULATE (
[Donation Amount],
ALL ( 'Date'),
FILTER (
ALL ( 'Event'[Event Year] ),
VALUE('Event'[Event Year] )
= EARLIER ( 'Event'[Event Year]) -1
)
)
)
I can't say I totally understand what it's doing, I have some reading to do 🙂
Thanks for your help, much appreciated.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply