How to create an R12 on a distinct count measure in SSAS/MDX?

0
votes

I have a distinct measure "No of Customer Id" that counts the amount of unique Customer Id's that occurs for each month. What I wish to do, is to create a Calculated member that distinct Count Customer Id over a 12 month period i.e Rolling 12. If you look at "Nr of Customer Id" in 201911 you have 378 unique Id's that month but between 20181201 and 20191130 there are 1020 unique Customer Id's (R12 Nr of Customer Id).

Is this possible to do and if so how ?

I tried with (But didn't work): sum(parallelperiod([D_Time].[Year-Month-Day].[Year],1,[D_Time].[Year-Month-Day].currentmember).lead(1) : [D_Time].[Year-Month-Day].currentmember , [Measures].[Nr of Customer Id])

enter image description here

Best regards, Rubrix

1
ssasmdxolap

1 Answers

0
votes

Do you need to use parallel period? Could you do something like:

(D_Time].[Year-Month-Day].currentmember.lag(12) :D_Time].[Year-Month-Day].currentmember,[Measures].[Nr of Customer Id])