TotalYTD for non-calendar date ranges

[RichardRayJH]

I work at a ski resort. Our primary operating season is from late November through April, with another operating season in the summer (Memorial Day through second week in October) and two non-operating 'shoulder seasons'. Everything I do is bounded by those dates: not quarters, not a fiscal year, not months, but by those 'seasons'. Sometimes weeks in the season are of interest (Winter 2012-2013 Week 8, etc.) or even a day over day comparison (Winter 2012-2013 Presidents Day vs. Winter 2011-2012 Presidents Day). I've figured out roll-my-own analogs for lots of the time intelligence functions, but before I wander off into the underbrush I thought I'd ask about this one....

Our Opening Day is always the Saturday after Thanksgiving, whatever date that may be. Our closing day is always the first Sunday in April, whatever date that might be. I've got a table with those dates (as well as dates for other 'moveable feasts' throughout the year), so calculating the dates isn't part of the challenge. What I want here is a 'SeasonToDate' function.

i.e. - Given that this year we opened on 2012-11-24 and will close on 2013-4-7, how do I do a generic function similar to the native DAX TotalYTD function? The built in version lets me stipulate the End of Year date, but not the start of year.

I've considered layering calls to the existing TotalYTD functions to do something like this (using the current season as an example):

SeasonToDate := (TotalYTD(for the year 2012 ending 12/31/2012)-TotalYTD(for the year 2012 ending 11/23/2012))+TotalYTD(for the year 2013 ending 2013/4/7)

I think that would work, but am hoping there's something a little less compute intensive. My data sets are in the 10's of millions of of rows and the top level report compares seasonal performance over the last 5 seasons, so I'd like to minimize the number of times I have to sum various parts of the data set....

 

[miguel.escobar]

Define a new column within your dates table that defines those periods. (please use a numeric field) Once you have that all you need to do is create a measure like =CALCULATE( SOMETHING, FILTER( ALL(Dates Table), newcolumn = new column && dates <= dates)). If you need more help with this please post your dates table so we can look at it.

 

[RichardRayJH]

I found a very complete explanation in Rob Colle's book in the section on GFITW (Greatest Formula In The World). It's the same pattern Miguel provided above. I think it's covered in his blog, too.

I'm a bit concerned about all that FILTER() manipulation in therms of performance, but I'll nail to up and see how it looks.

 

[Jacob Barnett]

Because you are filtering the calendar it shouldn't be that big of a deal in performance terms, that kicks in when you filter the fact table.

That said I would personally add a column to my calendar to dynamically flag Season to date - this keeps your measures simple as you can use a straight CALCULATE() and it can also be used in a report filter if necessary. This can be done as a DAX calculated column or in your data import.

Jacob

 

[RichardRayJH]

OK, I'm still stumped. There are several things I've already got working that I need to be able to keep working and have the cumulative calculation work with.

I've randomized the proprietary data (no, ski resorts don't gross in the 100's of millions per day . Can I upload it someplace so you (or anyone else) can see what I'm trying to accomplish?

 

[miguel.escobar]

you can try dropbox, mega, skydrive or just email me @