Prior Week Caculation


I have a field called Billings - Net Amt, and have a time demesion broken down by year and week. I need to create a caculation called Billings - Net Amt Prior Year - Week. How would you do this?




3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • The exact solution depends on how your dimension is constructed.  Are you asking about getting the amount from the same week, one year back?  Anyways, if your weeks are laid out such as "Week 1, 2016, Week 2, 2016, etc."  then you could do something like

    ([Date Dimension].[Week Hierarchy].CURRENTMEMBER.LAG(51), [Measures].[Billings - Net Amt])
    Reply Like 1
  • You can also use time hierarchy specific logic that accounts for time specific issues like Leap Years and such.  The ParallelPeriod function takes the ancestor of the specified member at the specified level, finds the ancestor's sibling with the specified lag, and finally returns the parallel period of the specified member among the descendants of the sibling.

    ParallelPeriod ([Date].[Calendar].[Calendar Year]  , 1 , [Date].[Calendar].[Week].CURRENTMEMBER)

    You can create a calculated member with the syntax above, using the Calc Designer. 

    In the Designer select New Data Point, choose your measure, then on your Year-Week hierarchy expand the Functional Selection folder and scroll to the bottom and choose ParallelPeriod, then select Year from the drop down menu next to it.  In your Data Discovery report, select a Week from the hierarchy and the new measure will return the measure value for that week in the prior year.


    Hope that helps,

    Reply Like 1
  • Thank you Tim and Dan; I needed help to do something similar and your answers helped greatly.

    Reply Like
reply to topic
Like Follow
  • Status Answered
  • 11 days agoLast active
  • 3Replies
  • 211Views
  • 4 Following