MDX Monday
#MDXMonday, MDX, SSAS

#MDXMonday: Previous Year Month to Date Calculation

I recently had a client give us a call asking for helping creating a calculation in her cube to show the month to date value for the previous year. Here’s the basics of the calculation I created.

View last week’s #MDXMonday –> Calculating Daily Average Sales

This calculation involve two parts. First we must create a calculation that figures the month to date value of the measure in question. To do this I used the MTD function along with the Aggregate function to roll up the Internet Sales Amount for each day in a month.

** Keep in mind that your date dimension and date attribute Type properties must be configured correctly in order for you to be able to use MDX time functions such as MTD, WTD, QTD, and YTD.

with member [Measures].[MTD Internet Sales] as 

    Aggregate(
        MTD([Date].[Calendar].currentmember),
        [Measures].[Internet Sales Amount]
        )

Select {
        [Measures].[Internet Sales Amount],
        [Measures].[MTD Internet Sales]
        } on 0,

Non Empty
[Date].[Calendar].[Date].members on 1

From [Adventure Works]

Where [Date].[Calendar Year].&[2012]

 

image

Now that we have the correct MTD calculation, but we need to modify this calculation to retrieve the MTD numbers for the previous year. To do that we can use the ParallelPeriod function. The ParallelPeriod function is used to navigate along a calendar hierarchy at a particular level. In this case we can use ParallelPeriod to go one member back on the Year level of the Calendar hierarchy like so:

with member [Measures].[PY MTD Internet Sales] as 

    Aggregate(
        MTD(
            ParallelPeriod(
                [Date].[Calendar].[Calendar Year],
                1,
                [Date].[Calendar].currentmember
                )
            ),
        [Measures].[Internet Sales Amount]
        )

    

Select {
        [Measures].[Internet Sales Amount],
        [Measures].[PY MTD Internet Sales]
        } on 0,

Non Empty
[Date].[Calendar].[Date].members on 1

From [Adventure Works]

Where [Date].[Calendar Year].&[2012]

 

image

And that’s how you create a calculation to figure the month to date for the previous year. Just an FYI, this calculation could also be used to calculate the previous year to date by swapping the MTD function for the YTD function.

Resources

Feedback?

Got any feedback on this blog post? Or do you have a calculation you’d like me to work on? Let me know in the comments! Thanks!