MDX : create calculated measure for specific month

Question

Hello,

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 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

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?

Thank you for your help

Answer 1

Hi @Landraille ,

Welcome to Microsoft Q&A!

Your MDX scripts are running correctly, because first measure will return previous year, it's 12 months. Second measure and third measure have been specified to December, so the results are same values.

And you could use StrToMember function to return the member specified.

ParallelPeriod (MDX) will returns a member from a prior period in the same relative position as a specified member.

Best regards,
Carrin

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @CarrinWu-MSFT
Thank you for your answer.
I finally find the problem. 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.