1

I am trying to exclude the period P13 (special period at the end of the fiscal year) for some time relative calculations, like a Sum using ParallelPeriod over 12 Months, or an average using Lag(). Here is a MDX so far:

WITH MEMBER [Measures].[Sum Amount 12 Months] AS Sum( ParallelPeriod( [Time].[Fiscal Calendar].[Fiscal Period],11):[Time].[Fiscal Calendar] , [Measures].[Amount] ) Periods in my hierarchy are like: 2014-P1,...., 2014-P12,2014-P13,2015-P1,...

So the idea would be first to simulate a removing of all periods -P13, then aggregate... But I really need help for this...

Thanks

Amael
  • 11
  • 1

2 Answers2

0

You can use the EXCEPT function to get rid of the P13.

WITH SET P13PeriodMember
AS TAIL(EXISTS([Time].[Fiscal Calendar].[Fiscal Period].MEMBERS, [Time].[Fiscal Calendar].[Fiscal Year].CURRENTMEMBER), 1).ITEM(0).ITEM(0)

MEMBER [Measures].[Sum Amount 12 Months]
AS 
SUM(
    EXCEPT(
    ParallelPeriod([Time].[Fiscal Calendar].[Fiscal Period].currentmember,11):[Time].[Fiscal Calendar].[Fiscal Period].currentmember, 
    P13PeriodMember),
    [Measures].[Amount]
)
SouravA
  • 5,147
  • 2
  • 24
  • 49
  • I see what you want to do with that, but I got an error because I can't use CURRENTMEMBER on [Time].[Fiscal Calendar].[Fiscal Year] (error: ...CURRENTMEMBER is waiting for a hierarchy expression...a member expression was used). – Amael Jan 13 '15 at 21:38
  • So, in order to test I hardcoded the set P13PeriodMember with some P13 members, then did the Except like you did, but the Sum is still using P13.. And like I said I can't use CURRENTMEMBER on [Time].[Fiscal Calendar].[Fiscal Period]... – Amael Jan 13 '15 at 22:43
0

Here is the MDX now working, Sourav_Agasti answer was really helpful, but an issue was still there concerning the ParallelPeriod parameter. Indeed, if we exclude a member from the Set we still need to increment our parameter in order to add 1 period...

There is probably an easier solution and I will be glad to hear it!

WITH 
SET P13Periods
AS 
{       //Exclude P13 members (hardcode for test)
        [Time].[Fiscal Calendar].[Fiscal Period].&[201013],
        [Time].[Fiscal Calendar].[Fiscal Period].&[201113],
        [Time].[Fiscal Calendar].[Fiscal Period].&[201213],
        [Time].[Fiscal Calendar].[Fiscal Period].&[201313]
}


MEMBER [Measures].[Amount Sum 2 Months]
AS 
SUM(
    //Check if range Period contains a P13
    //If yes, then we need to exclude this period from calculation AND add 1 Period to ParallelPeriod
    IIF(
         EXISTS(
            P13Periods,
            ParallelPeriod([Time].[Fiscal Calendar].[Fiscal Period],1):[Time].[Fiscal Calendar]
            ).Count =1
        ,EXCEPT(
            ParallelPeriod([Time].[Fiscal Calendar].[Fiscal Period],2):[Time].[Fiscal Calendar]
            ,P13Periods
            )
        ,ParallelPeriod([Time].[Fiscal Calendar].[Fiscal Period],1):[Time].[Fiscal Calendar]
        )
    ,
    [Measures].[Amount] 
)

SELECT  {[Measures].[Amount],[Measures].[Amount Sum 2 Months]} on 0
        ,[Time].[Fiscal Calendar].[Fiscal Period] on 1
FROM [MyCube]
Amael
  • 11
  • 1