DAX : YTD on 445 Calender and Irregular End Dates


In a previous post I looked at the built in DAX Total to date functions (TotalYTD, TotalMTD, TotalQTD) which sum calendar periods to an end date. The YTD function is somewhat flexible because it allows a year end date to be passed as an optional argument in the function. However, the end date is static in the formula, that is, the same year end date is applied must be applied to all years (also indicating the start of the next year is the day after the prior year ends). The 445 calendar contravenes this requirement because the yearend date (and year start date) is not consistent between years.

The 445 Calendar

The 445 calendar (and variants) are a commonly used to standardise months so that there are an even number of weeks in each month.One of the side effects of this is that the year does not start and end on the same day each year (note 2010 starting on 3-Jan and 2-Jan in 2011). This means that functions that rely on dates cannot be used to aggregate yearly totals.

The Formula

The calculation works by simply removing the current date filter context and then reapplying it so that the year is restricted to the current fiscal year and the date is less than the maximum date for any filter (ie row, column, filter axis) that is applied. The use of max for the fiscal_year_key is simply to return the current year as a scalar.


Using the totalytd function, the YTD value for December 2010 is effectively calculated as the YTD value for a date which ends on for 1-Jan-2011 (1 day).This does not occur when the filter context is reapplied because the calculation includes all data for the current year that is below the last date of the current filtered year (that is, all dates up to and including 28-Nov-2010).

An alternative approach that deals with text running balances can be found at Javier Gullen‘s blog.

Advertisements

5 thoughts on “DAX : YTD on 445 Calender and Irregular End Dates

  1. What values should I use for Dates[fiscal_year_key] and Dates[date_alt_key]? I get the correct value if I use literals for the right side of = and <= in the filter, but cannot get this to work for dynamic dates.

  2. hi, how can I get the YTD for last-year, using a 445 calendar? for my current year I’m using the standard TotalYTD formula, as I need to aggregate on the ‘normal’ calendar. but when comparing with last-year, I need to compare using the 445.. (essentially today will be compared with today-364, and I need that rolled up to the Y/Q/M grains when comparing with lastyear)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s