How can I create an SSAS calculated member which sums prior year up to current date (in prior year)?

Question

I'm confident that this has been addressed before, but I haven't been able to find it. I have found answers relating to individual MDX queries for a given date, but I don't know how to translate that into a Calculated Member of an SSAS cube that is going to work regularly.

I'd like to create a calculated member in SSAS which sums revenue from start of the previous year to the currentmember, but stops and the current date in the previous year. E.g., today is 10/16/2013. I'd like this calculated member to provide a YTD sum which includes revenue to 10/16/2012. This is so that the rollup of Q4 2012 includes only the first 16 days of Q4 2012, rather than the entire year.

Here is the current definition of the prior year to date calculated member:

SUM(
        YTD(
        ParallelPeriod([Time Order Date].[Order Date Calendar Hierarchy].[Year], 
        1,
        [Time Order Date].[Order Date Calendar Hierarchy].CurrentMember
        )
    ),
        [Measures].[Product Rev (with ship, with disc)]
    )

FWIW, my time dimension table has all of the day of year, day of quarter, day of month, etc columns that could be used.

Thanks, --sw

Doran Doran · Accepted Answer · 2013-10-17T20:11:41

I am also interesting in the question, however I would not use the YTD function as it can only be used with a Calendar year. Using your data elements and hierarchy I would change it to use the PeriodsToDate function:

Aggregate(  PeriodsToDate(  [Time Order Date].[Order Date Calendar Hierarchy].[Year]
                            ,ParallelPeriod(    [Time Order Date].[Order Date Calendar Hierarchy].[Year]
                                                    ,1
                                                    ,[Time Order Date].[Order Date Calendar Hierarchy].CurrentMember
                                                )
                             )
                ,[Measures].[Product Rev (with ship, with disc)]
             )

How can I create an SSAS calculated member which sums prior year up to current date (in prior year)?

1 Answers