Using non-calendar date ranges for cumulative functions

[RichardRayJH]

I work for a winter resort (Jackson Hole, Ski Resort, Skiing, Snowboarding, Vacation Packages and Ski Trips | Jackson Hole Mountain Resort | Jackson Hole Mountain Resort | Jackson, Wyoming). Like most winter resorts our operating season spans the normal calendar year; it normally runs from a date in the last half of November to one in the middle of April of the following year. Opening and Closing dates are determined by a day of the week: we always open on a Saturday in November and close on a Sunday in April, but it might be any of the Nth such days in the month. I have a table (Season Dates) with each season's Opening and Closing Dates, as well as dates for holidays like MLK Day, Presidents Day and Easter that move around each year.

All Year To Date (YTD) calculations of interest are actually Season To Date (STD). The standard date functions in PowerPivot for YTD, etc. seem pretty useless for my needs. Can anyone suggest a generalized approach to this problem?

For instance: I have a Net Revenue column with daily net by date. How do I calculate a STD sum for a date range beginning on Saturday, 11/28/2013 running through Sunday 4/6/2014? And, do it again for Saturday, 11/24/2012 through Sunday, 4/7/2013. Etc, etc., back through the seasons?

What I'd like, I think, would be a way to create a surrogate for the built in YTD functions to which I could feed my own definitions of the start and end dates. Does that make sense? Another approach?

TIA

 

[JNM]

I'm looking for a similar fix. Our "for example" - start date 10/1/2010, end date 9/30/2013. Group by Year starting with start date and show three headers as follows - 10/1/2010..9/30/2011, 10/1/2011..9/30/2012, 10/1/2012..9/30/2013. This would be dynamic depending on the start date, but would always be grouped by annual period. Start dates are always the first day of a month, end dates are always last day of a month.

Sometimes they want only three months or sometimes only six months. So for that example - 9/1/13..11/31/13 time span and column header.

I was think something along the lines of a calculated column called #ofYrs based on the posting date column in a filtered table (filtered on a start date and an end date) that would evaluate the posting date and if within 1 year of Start Date, it would be 1, more than 1 year, but less than 2 years = 2, etc., then a second calculated column that would create the Dynamic Header using a calculation something like If #ofYrs = 1, Start..Start +1 Yr, if#ofYr=2, then Start+1 Yr..Start+2 Yr, etc.

I know DAX Measures can reference an unlinked slicer value. Will need to try and figure this out.

 

[elliotstan]

Hi,
I have a problem close to what you are after JNM and RichardRayHJ...
but what you need is something like

=CALCULATE(sum(Table1[sales]),
                  DATESBETWEEN(datum[Date],
                                  FIRSTDATE(DATEADD(datum[Date],-12,MONTH)),
                                  LASTDATE(DATEADD(Table1[Date],-12,MONTH))

The DATESBETWEEN formula is probably a good start for what you need.
Kasper de Jonge has some info
Try this first
Kasper de Jonge PowerPivot and Power BI Blog | Use PowerPivot DAX to get values within a Start and End date
Then this
Kasper de Jonge PowerPivot and Power BI Blog | Get the YTD of same period last year using DAX

Good luck