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]
In this post, I shared a MDX query that you can use for current fiscal period vs previous year same fiscal period.