YTD Calculation in Query Designer

One of the most common requested aggregations in MDX is YTD. There are many sources that highlight how to do this in the Cube development environment, but sometimes you don’t have access to the SQL Data Tools / BIDs.

I find using the native YTD calculation problematic when attempting to make YTD calculation outside of the Cube development environment. The YTD function can be extremely slow to process outside of the Cube development environment.  Also, if you’re like me many times you only have access to read the cube but not develop against it. You should then you use the Query Builder to create an YTD calculation.

Basically use the Calculated Member Builder in the Query Designer (whether in Report Builder or Visual Studio).

In my example, I use the following data to create the YTD calculation against the Date Hierarchy PostingPeriod.Fiscal and the Measure.LaborCost.

Aggregate

 (
 PeriodsToDate
 (
[Posting Period].[Fiscal].[Fiscal Year]
 , [Posting Period].[Fiscal].CurrentMember
 )
 ,[Measures].[Labor Cost]
 )

 This how looks in the Calculated Member in Query Designer…

  This is one of the most useful features of the Calculated Member tool – allowing you to create dynamic aggregations without cube authoring..