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