1 Reply Latest reply on Jan 17, 2014 9:13 AM by Brad Lynch

    Rolling 12 month date filter using MDX code

    Kory Shea

      Hello. I am trying to create a rollling 12 month filter against a cube created in MS Analysis services and not sure where to start. I am not familiar with MDX code. The date field is [Date - Loan Origination].[Calendar].[Date]. Any suggestions?

        • 1. Re: Rolling 12 month date filter using MDX code
          Brad Lynch

          By rolling, do you mean from today's current month and the previous 12 months?

           

          One way to approach this would be in your cube design. Assuming you're using Microsoft SSAS, you could constrain your date dimension to the current month and no further into the future. Then in your daily processing of the cube, your newest month in the date dimension is the current month. (there are more elegant solutions out on the internet using VBA functions that you could check out).

           

          Then you could build a named set that pulls in all of the month members and then something like a TAIL( [Date - Loan Origination].[Calendar].[Date].[Month].Members, 12) would get you the last 12 in the set, assuming they are ordered in their correct sort order.

           

          brad