Advanced last year comparison for a tour operator

[perell]

Hi

With my travel search dataset I've managed to create a last year comparison of searches over a given period of time. Ie. "Search Week this year" vs "Search Same week last year". Theese metrics will include searches of all available departure periods in a date format, ie. "2016-07-01".

What I would like do is to filter on the same departure month last year. If I use filter or slicer on the departure month "2016-07" there is no data from last year since the same departure month last year was "2015-07".

I've createted a calculated metric "months until departure" but I havent figured out how to translate this metric back to a month format like "2016-07" like in the table below.

[TABLE="width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]Departure month: 2016-07[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week[/TD]
[TD]Search TY[/TD]
[TD]Search LY (2015-07)[/TD]
[/TR]
[TR]
[TD]2016-05[/TD]
[TD]2500[/TD]
[TD]1900[/TD]
[/TR]
[TR]
[TD]2016-04[/TD]
[TD]2000[/TD]
[TD]1800[/TD]
[/TR]
</tbody>[/TABLE]

Just to clarify the problem with another example, how would you compare sale of iphone 6 (when relased) to iphone 5 at an earlier period of time where you dont just compare sale of all models of iphone?

Any idea how to approach this sort of comparison where the "product" differs from year to year?

Thank you
Per

 

[Matt Allington]

This is a common requirement and easily handled in DAX. But there are layers and layers of learning and complexity to understand it. I can't teach you everything here (although you can buy my book - see the signature below). The basic principle is that you set up a calendar table in your data model. Read about calendars here Power Pivot Calendar Tables -

Then depending if you use a standard calendar or not, you can use an inbuilt function like this

Sales Last Year :=calculate(sum(sales[amount]),sameperiodlastyear(calendar[date]))

the above formula performs a time shift for you provided you follow the calendar rules in the link I provided.

If if you don't have a standard calendar, you need a more complex formula using filter (typically) to do the calculation.