CODEWITHSUNDEEP
ssasmdxssas-2008ssas-2012mdx-query
xanhdieu
xanhdieu

Reputation: 43

SSAS MDX: Time Calculation with a date range

In my cube, I have a Scope statement for the Previous Year Same Period [PY SAME Period].

([Time Calculations].[PY Same Period]=
Aggregate(
    CrossJoin
            (
             {[Time Calculations].[Current Period]},
             PARALLELPERIOD(
               case [Date].[Date Hierarchy].CurrentMember.LEVEL_NUMBER
                   when 4 then [Date].[Date Hierarchy].[Date]
                    when 3 then [Date].[Date Hierarchy].[Month]
                    when 2 then [Date].[Date Hierarchy].[Quarter]
                    when 1 then[Date].[Date Hierarchy].[Year]
               end,
                  case [Date].[Date Hierarchy].CurrentMember.LEVEL_NUMBER
                    when 4 then 364
                    when 3 then 12
                    when 2 then 4
                    when 1 then 1
                   else null  
                   end ,  [Date].[Date Hierarchy].CurrentMember  )
             ))

);

This works fine for one date but doesn't work for a range of dates, which includes Begin Date and End date.

[Date].[Date Hierarchy].[Date].&[2020-01-20T00:00:00] - This works


{[Date].[Date Hierarchy].[Date].&[2020-01-01T00:00:00]:[Date].[Date Hierarchy].[Date].&[2020-01-20T00:00:00]} - doesn't work

How do I get the scope statement to work for both scenarios?

Upvotes: 2

Views: 185

Answers (0)

Related Questions