SSAS/MDX : create calculated measure for specific month

I'm not friendly with MDX and I have some difficulties to create calculated measures. We have some data in a cube and the users would like to have also the measures of the previous month, the measures of december of last year and the measures of december of the current year.

We have our own calendar table in our database with the fields date, month, year, month number... I have created a Calendar Dimension based on this table with the Time type (with link between date -> Days, month -> Months and year -> Years ). I have added the measure in the cube.

I try to create the calculated measure like this :

• previous month :

  CREATE MEMBER CURRENTCUBE.[Measures].[MEASURE_A_PREVIOUS_MONTH] AS ([Dim_CALENDAR].[MONTH].CURRENTMEMBER.PREVMEMBER, [Measures].[MEASURE_A])

=> OK

• december last year :

  CREATE MEMBER CURRENTCUBE.[Measures].[MEASURE_A_DECEMBER_PREVIOUS_YEAR] AS IIF(ISEMPTY([Measures].[MEASURE_A]), NULL, ([Dim_CALENDAR].[MONTH_NUMBER].[12], [Dim_CALENDAR].[YEAR].CURRENTMEMBER.PREVMEMBER, [Measures].[MEASURE_A]))

=> It's good for the first year (2021-M01 to 2020-M12) but after I have a sum of all the month by year

• december current year

  CREATE MEMBER CURRENTCUBE.[Measures].[MEASURE_A_DECEMBER_PREVIOUS_YEAR] AS IIF(ISEMPTY([Measures].[MEASURE_A]), NULL, ([Dim_CALENDAR].[MONTH_NUMBER].[12], [Dim_CALENDAR].[YEAR].CURRENTMEMBER, [Measures].[MEASURE_A]))

=> It's good for the first year (2019-M12) but after I have a sum of all the month by year

image of cube results

It seems i can't use the PARALLELPERIOD function because I would like a specific month (december) isn't it ?

What could be the issue?

Solution 1:

OK find the solution, it comes from my Calendar dimension, I had a bad relation, I had the relation DATE-> MONTH_NUMBER instead of MONTH -> MONTH_NUMBER. Now it's work.