# 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]

Conclusion:

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];