Time Intelligence for irregular events

[CalumSharp]

Hi, I'm trying to leverage the Time Intelligence functions (specifically SamePeriodLastYear and/or DateAdd) to compare transactional calls data year on year for an event that doesn't occurr on the same date each year (think Easter, but even less predictable).

The logic I'm using is the Date Dimension in our Warehouse has a Offset calculation to this annual event. In PowerQuery I'm using this to calculate a "normalised" date, effectively mapping the actual event date to the 1st August for that year. However, for the user, I still want to show the real date. I'm expecting the Time Intelligence functions to thus be able to relate year on year using this normalised date.

The data set only has dates around the event date in so there won't be any overlap in the "normalised" dates.

I've included the Date Dimension table in the model, set it as a Date Table and joined it to the fact table on the Normalised Date column.

I have 2 measures:

CallsAnswered = Calculate(sum(AllCalls[Calls]), AllCalls[Outcome] = "Answered")

CallsAnswered_LastYear = calculate([CallsAnswered], SAMEPERIODLASTYEAR(Dates[CalendarDate]))

The results I'm getting are not what I'm looking for - the "LastYear" version of the measure is just returning the same value as the normal one.

I've put a simple starter report file and sample data as CSV in this OneDrive folder (link expires May 31st)

https://1drv.ms/u/s!AkbLwllA_i3IwQu__B-IssQO-5gr?e=EEC3zw

[amitchandak · Accepted solution]

@CalumSharp , make sure dates is marked as a date table. and in visual any period comes from date table

try like

CallsAnswered = Calculate(sum(AllCalls[Calls]), filter(AllCalls,AllCalls[Outcome] = "Answered"))
CallsAnswered_LastYear = calculate([CallsAnswered], SAMEPERIODLASTYEAR(Dates[CalendarDate]))

or

CallsAnswered_LastYear = calculate([CallsAnswered], dateadd(Dates[CalendarDate],-1, year))

[CalumSharp]

@amitchandak wrote:

@CalumSharp, make sure dates is marked as a date table. and in visual any period comes from date table

That's the trick! The date dimension was marked as a date table, but I was using the date in the fact data because it's what the customer would expect to see. I've done the reverse translation in the dimension and it's working as desired.