none
How to get the previous month value from a cube using a date heirarchy

    Question

  • Hi

    I apologise in advance if this question has already been answered and for my lack of knowledge regarding cubes.

    I have a cube with a date dimension. I have setup the date dimension to be a heirarchy with 3 levels year - month - date. How do I use the heirarchy to get the previous month value inside an expression. I'm currently using the below expression but my problem is that this expression only works when I use the month key in my filter but its not working when I filter using the heirarchy in the date.

    (ParallelPeriod([Dim Date].[Month Key].[Month Key],1,[Dim Date].[Month Key].Currentmember),[Measures].[Material Transport Cost])

    Tuesday, June 12, 2018 11:09 AM

Answers

  • Hi,

    Try this :

    (ParallelPeriod([Dim Date].[HierarchyDate].[Month Key],1,ANCESTOR([Dim Date].[HierarchyDate].CurrentMember, [Dim Date].[HierarchyDate].[Month Key])),[Measures].[Material Transport Cost])

    OR

    (ParallelPeriod([Dim Date].[HierarchyDate].[Month Key],1,[Dim Date].[HierarchyDate].Currentmember.Parent),[Measures].[Material Transport Cost])


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Tuesday, June 12, 2018 11:21 AM

All replies

  • Hi,

    Try this :

    (ParallelPeriod([Dim Date].[HierarchyDate].[Month Key],1,ANCESTOR([Dim Date].[HierarchyDate].CurrentMember, [Dim Date].[HierarchyDate].[Month Key])),[Measures].[Material Transport Cost])

    OR

    (ParallelPeriod([Dim Date].[HierarchyDate].[Month Key],1,[Dim Date].[HierarchyDate].Currentmember.Parent),[Measures].[Material Transport Cost])


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Tuesday, June 12, 2018 11:21 AM
  • Hi,

    You could try writing a case statement and check what the level is and based on the level selected you can write your expression.

    Something like this

    CASE WHEN [Dim Date].[Date Hierarchy].CURRENTMEMBER.LEVEL IS [Dim Date].[Date Hierarchy].[MONTH]

    THEN (ParallelPeriod([Dim Date].[Date Hierarchy].[Month ],1,[Dim Date].[Date Hierarchy].Currentmember),[Measures].[Material Transport Cost])

    WHEN [Dim Date].[Date Hierarchy].CURRENTMEMBER.LEVEL IS [Dim Date].[Date Hierarchy].[Date]

    THEN (ParallelPeriod([Dim Date].[Date Hierarchy].[Month],1,ANCESTOR([Dim Date].[Date Hierarchy].CurrentMember, [Dim Date].[Date Hierarchy].[Month])),[Measures].[Material Transport Cost])

    ELSE NULL

    END

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    • Proposed as answer by alexander fun Wednesday, June 13, 2018 12:42 PM
    Tuesday, June 12, 2018 1:23 PM
  • Hi Kirk,

    Thanks for your question.

    >>>(ParallelPeriod([Dim Date].[Month Key].[Month Key],1,[Dim Date].[Month Key].Currentmember),[Measures].[Material Transport Cost])
    If above MDX is used to get the previous month value, Then you can simply try below MDX expression:

    ([Dim Date].[DateHierarchy].CurrentMember.PrevMember,
    [Measures].[Material Transport Cost])
    OR
    ([Dim Date].[DateHierarchy].CurrentMember.LAG(1),
    [Measures].[Material Transport Cost])

    OR
    ([Dim Date].[DateHierarchy].CurrentMember.LEAD(-1),
    [Measures].[Material Transport Cost])
    See below MDX query tested in cube AdventureWorks:
    With Member [Measures].[PreMonth1] AS
    [Date].[Calendar].CurrentMember.PrevMember.UNIQUENAME
     Member [Measures].[PreMonth2] AS
    [Date].[Calendar].CurrentMember.LAG(1).UNIQUENAME
     Member [Measures].[PreMonth3] AS
    [Date].[Calendar].CurrentMember.LEAD(-1).UNIQUENAME
    Select 
    {[Measures].[PreMonth1],
    [Measures].[PreMonth2],
    [Measures].[PreMonth3]} on 0,
    [Date].[Calendar].[Month] on 1
    From
    [Adventure Works]
    Where
    [Date].[Calendar Year].&[2013]



    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Wednesday, June 13, 2018 12:42 PM
    Wednesday, June 13, 2018 2:15 AM
    Moderator