CODEWITHSUNDEEP
mdxleap-year
Eric Blair
Eric Blair

Reputation: 117

MDX: How to go back 1 year-to-date while taking leap year into consideration?

Trying to come up with a query that will give me the set of dates going back 365 days from today (and in leap years past feb, 366 days).

Here's what I got so far:

Select [Measures].[Revenue] on 0, non empty {[Trans Date].[Year - Week - Date].[Date], PARALLELPERIOD([Trans Date].[Year - Week - Date].[Year], 1,[Trans Date].[Year - Week - Date]) }on 1 from Cube

This but this goes back to the beginning of "time". I just need it to go back 365 (or 366 of leap year.)

Thought of using Lag, but not quite sure ho it would fit in this scheme. Anyone have any advice on this?

Thanks!

Upvotes: 0

Views: 1939

Answers (1)

Tullo_x86
Tullo_x86

Reputation: 2673

First, I'm going to reformat your query for readability.

SELECT [Measures].[Revenue] ON 0,
       NON EMPTY 
       {
         [Trans Date].[Year - Week - Date].[Date],
         PARALLELPERIOD([Trans Date].[Year - Week - Date].[Year],
                        1,
                        [Trans Date].[Year - Week - Date])
       } ON 1
FROM Cube

What you have here is a set of the entire Date level ([Trans Date].[Year - Week - Date].[Date]), and additionally a single member from that dimension (the result of the PARALLELPERIOD call).

What you want is a range of members, which is accomplished with the range operator :. For example,

NON EMPTY
{
  PARALLELPERIOD([Trans Date].[Year - Week - Date].[Year],
                 1,
                 [Trans Date].[Year - Week - Date].[Date].CURRENTMEMBER)
  :
  [Trans Date].[Year - Week - Date].[Date].CURRENTMEMBER
} ON 1

<3

Upvotes: 0

Related Questions