MDX query help in getting selected items from dimension

jehenjoshi jehenjoshi 21 Reputation points

I have a dimension called service date. This date has two attributes- month and rolling band as
Service Date Rolling Band
[2020 - NOV] 0
[2020 - DEC] 1
[2021 - JAN] 1
[2021 - FEB] 1
[2021 - MAR] 1
[2021 - APR] 1
[2021 - MAY] 1
[2021 - JUN] 1
[2021 - JUL] 1
[2021 - AUG] 1
[2021 - SEP] 1
[2021 - OCT] 1
[2021 - NOV] 1
[2021 - DEC] 0

For getting months from [2020 - DEC] to [2021 - NOV], I can simply do
as aggregate ([Service Date].[Service Date].[Service Months],[Service Date].[Rollingband].&[1]).
But I need to aggregate months from [2021 - JAN] to [2021 - DEC].
Is there any query to get this?

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,243 questions
0 comments No comments
{count} votes

Accepted answer
  1. Alexei Stoyanovsky 3,411 Reputation points

    If you happened to have a calendar year attribute in your Service Date dimension, you could just use the year 2021. If you don't, do consider adding it to the dimension.
    Otherwise, the simplest way to write a set of consecutive elements with MDX is the range operator, colon.
    As a side note, I can't think of a reason for aggregating a member of one attribute of a dimension over a set of members of another attribute of the same dimension, as in your sample MDX.

1 additional answer

Sort by: Most helpful
  1. Darren Gosbell 2,371 Reputation points

    If you just want to get the last 12 members of the month attribute you could also do AGGREGATE( TAIL( [Service Date].[Month].Members, 12) )

    0 comments No comments