Feeds:
Posts

## How to calculate YTD according to the Australian Financial Year in MDX (i.e. from July to June)?

Normally, the YTD calculation is based on the calendar year i.e. from January of the report year selected to the date selected.

I have a report requirement where the Year To Date figure is to be based on the Australian Financial Year. If the report month selected is less than July 2011, the YTD  calculation would be from 1 July 2010 to 30 June 2011. If the report month selected is greater than or equal to July 2011, the YTD calculation would be from 1 July 2011 to 30 June 2012.

MDX to do this is as below:

WITH
SET [LastDayOfMonth] AS CLOSINGPERIOD([Date].[Monthly].[Date], STRTOMEMBER(“[Date].[Monthly].[Month].&&”, CONSTRAINED)  )
SET [FirstDayOfMonth] AS  OPENINGPERIOD([Date].[Monthly].[Date], STRTOMEMBER(“[Date].[Monthly].[Month].&&”, CONSTRAINED)  )
CASE WHEN CINT(Format([LastDayOfMonth].Item(0).MemberValue,”MM”))  >= 7 THEN Format([LastDayOfMonth].Item(0).MemberValue, “yyyy”)
ELSE CSTR(CINT(Format([LastDayOfMonth].Item(0).MemberValue, “yyyy”)) – 1) END
+ “]&” ), 4), 1)

SET [ToDate] AS Tail(Descendants(STRTOMEMBER(“[Date].[Monthly].[Month].&[“+
CASE WHEN CINT(Format([LastDayOfMonth].Item(0).MemberValue,”MM”))  < 7 THEN Format([LastDayOfMonth].Item(0).MemberValue, “yyyy”)
ELSE CSTR(CINT(Format([LastDayOfMonth].Item(0).MemberValue, “yyyy”)) + 1  ) END
+ “]&” ), 4), 1)

MEMBER [Measures].[Fiscal YTD Injuries HardCoded] AS SUM({[Date].[Monthly].[Date].&[2010-07-01T00:00:00]: [Date].[Monthly].[Date].&[2011-06-30T00:00:00]},[Measures].[Injury Count])
MEMBER [Measures].[Fiscal YTD Injuries] AS
SUM({STRTOMEMBER(“[Date].[Monthly].[Date].&[” + Format([FromDate].Item(0).MemberValue, “yyyy-MM-dd”) + “T00:00:00]”)
: STRTOMEMBER(“[Date].[Monthly].[Date].&[” + Format([ToDate].Item(0).MemberValue, “yyyy-MM-dd”) + “T00:00:00]”) }
,[Measures].[Injury Count])
SELECT { [Measures].[Injury Count]
, [Measures].[Fiscal YTD Injuries]
, [Measures].[Fiscal YTD Injuries HardCoded]
} ON COLUMNS
,{[FromDate], [ToDate] } ON ROWS
FROM INX_Cube