MDX query to group on year of date dimension with specific date range

Question

I have SQL query SELECT YEAR(DATE),SUM(AMOUNT) FROM TABLE WHERE DATE BETWEEN '2017-09-01' AND '2018-09-30' GROUP BY YEAR(DATE).

This will give me sum of amount for 2017 and 2018 for selected date range. Same I am trying in mdx query with date dimension

SELECT {[Date].[year].CurrentMember*[Measures].[departureUpsellRevenue]} ON COLUMNS,{[Date].[2017].[09].[1]:[Date].[2018].[09].[30]} ON ROWS FROM [CUBE]

its giving me error Mondrian Error:Hierarchy '[Date.calendar]' appears in more than one independent axis.

Answer 1

Try a sub select

SELECT 
[Date].[year].[year].MEMBERS ON 0
,[Measures].[departureUpsellRevenue] ON 1
FROM 
(
SELECT
{[Date].[2017].[09].[1]:[Date].[2018].[09].[30]} ON 0
FROM [CUBE]
);