SQL server analysis services MDX: Current Fiscal Period vs Previous year same Fiscal Period Query

Standard

Problem:

Comparing data current fiscal period vs previous year same fiscal period query is a very common problem. Here’s a sample query.

Solution:

Please use the query as a staring point for current fiscal period vs previous year same fiscal period. I used the Contoso Cube to come with the query. The key part is to use the PARALLELPERIOD function.

[code language=”SQL”]

with member [Sales Amount Previous Year Fiscal Month] as
SUM(ParallelPeriod([Date].[Fiscal Month].[Fiscal Month],12,[Date].[Fiscal Month].CurrentMember ),[Measures].[Sales Amount] )

select
{[Measures].[Sales Amount],[Sales Amount Previous Year Fiscal Month]} on 0,
NON EMPTY {[Date].[Fiscal Month].[Fiscal Month]} on 1
from
[Sales]

where
[Date].[Fiscal Year].&[2008]

[/code]

MDX current year vs previous year same fiscal periodConclusion:

In this post, I shared a MDX query that you can use for current fiscal period vs previous year same fiscal period.

3 thoughts on “SQL server analysis services MDX: Current Fiscal Period vs Previous year same Fiscal Period Query

  1. Chacika

    I have a similar issue. I want 2(or more) years in my filter, months as rows and compare my measures side by side. Is there a possible solution for this?

    • PARALLELPERIOD function should be able to help you do that. you’ll need to adapt from the example that I’ve – in place of “12” that I have at month level, you’ll need 24 to go back 2 years. If you have a year level in the parallelperiod in your code, the use values accordingly.

      • yuri

        Hello paras,

        I have a calculated measure ‘sales usd Fiscal Year to date’…..but the behavior expected is not really a
        a year to date, it must be based on current date, so if the cube is run for today, which is 3/6/2015, then give me all sales starting from 1st date of fiscal year to ‘Up to Current date’. This has to apply for all fiscal years, 3/6/2014, 3/6/2013, etc….
        How to put a filter for current date….?

        WITH MEMBER [Measures].[Sales – USD FYTD] AS
        Aggregate(
        PeriodsToDate(
        [Date].[Fiscal].[Fiscal Year] –specify the LEVEL expression in a hierarchy
        , [Date].[Fiscal].CurrentMember –DYNAMIC MEMBER expression
        ), [Measures].[Sales – USD]
        )
        Select [Measures].[Sales – USD FYTD] ON COLUMNS,
        –[Date].[Fiscal].[Fiscal Year].&[2014] ON ROWS –works
        [Date].[Fiscal].[Fiscal Year].Members ON ROWS –works – display all member values (all fiscal years)

        From [Sales Cube];

What do you think? Leave a comment below.