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 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.