Report Builder 3.0 - MDX - Moving Sum

Question

I have poured over quite a few websites, including this one, and so far in vain. I need to create a graph depicting the employee turnover rate, which has to be calculated over 1 year. (As stated in my title I have the option of either going with 13 periods or 12 months: both are an equivalent of 1 year.) Please see the following "sample version" of my dataset:

Year  |  Trimester  |  Period  | Date End of Period | Departures  |  Avg total Employees
2015         1          1        yyyy-mm-dd 00:00:00      2           100
2015         1          2        yyyy-mm-dd 00:00:00      3           99
2015         1          3        yyyy-mm-dd 00:00:00      4           98
2015         2          4        yyyy-mm-dd 00:00:00      0           102
2015         2          5        yyyy-mm-dd 00:00:00      1           100
2015         2          6        yyyy-mm-dd 00:00:00      0           98
2015         3          7        yyyy-mm-dd 00:00:00      4           99
2015         3          8        yyyy-mm-dd 00:00:00      3           96
2015         3          9        yyyy-mm-dd 00:00:00      4           100
2015         3          10       yyyy-mm-dd 00:00:00      0           98
2015         4          11       yyyy-mm-dd 00:00:00      0           97
2015         4          12       yyyy-mm-dd 00:00:00      2           99
2015         4          13       yyyy-mm-dd 00:00:00      3           98
2016         1          1        yyyy-mm-dd 00:00:00      2           100
2016         1          2        yyyy-mm-dd 00:00:00      4           97
2016       [...]

FYI Avg total employees= ([Total employees].[End of Period] + [Total employees].[Start of Period])/2

Here's what my graph currently looks like (apologies, the labels are in French):

Value: Departures / Avg total employees By the way, as you can surely notice, the orange was an attempt to obtain the desired result by going to Add Calculated Series and Calculated Series Properties choosing moving average: the attempt was a fail.

So here is my problem: all I want is 1 serie that displays a 12 months cumulated value for each period. That is, please see following example based on my Dataset above:

Current for Year 2016, Period 1 = 2/100 = 2%

Wanted result for Year 2016, Period 1 = (2+3+2+0+0+4+3+4+0+1+0+4+3)/((100+100)/2) = 26%, i.e. to add the departures of the last 13 periods and making a ratio over the avg number of employees: (2016P01+2015P13+2015P12+[...]+2015P03+2015P02)

So, in other words, I need to obtain a graph where the turnover value displayed at each period would be a percentage that surely would range around 20-30% and not the current 0-4% (based on my dataset values.) Any ideas?

Thanks to all!

EDIT1 I found out that by using the RunningValue function: =RunningValue(Fields!Turnover.Value, Sum, Nothing) it gives something close to what I want but not quite it still. I would basically need a "Runningtotal" with a scope of last 13 periods... if that exists =/

p.s. Fields!Turnover.Value = Fields!Departure.Value / Fields!Avg_total_Employees.Value

EDIT2 Firstly, thank you Alan. Going fwd, here's a screenshot sample of what my actual DS:

Note: The slight difference between the one I typed up and this one is that I am required to provide a turnover Tx1 (all employees included) and a turnover Tx2 (the retirements excluded). Therefore, I created the following calculated members (bottom left corner of the screenshot) and removed from my DS the [Avg total employees] and [Departures] since my Tx1 and Tx2 accounted for both:

Number of retirees: Départs_ret = ([Measures].[Nb Départs],[Départ].[Départ].&[112337])

Turnover1: Tx1 = ([Measures].[Nb Départs])/([Measures].[Nb Matricules Actifs (Moyen)])

Turnover2: Tx2 = (([Measures].[Nb Départs])-([Measures].[Départs_ret]))/([Measures].[Nb Matricules Actifs (Moyen)]))])

My MDX query in text form is the following:

WITH MEMBER [Measures].[Départs_ret] AS ([Measures].[Nb Départs],[Départ].[Départ].&[112337]) 
MEMBER [Measures].[Tx1] AS ([Measures].[Nb Départs])/([Measures].[Nb Matricules Actifs (Moyen)]) 
MEMBER [Measures].[Tx2] AS (([Measures].[Nb Départs])-([Measures].[Départs_ret]))/([Measures].[Nb Matricules Actifs (Moyen)]) 
SELECT NON EMPTY { [Measures].[Tx1], [Measures].[Tx2] } 
ON COLUMNS, NON EMPTY { ([Période Financière].[PF - Année Financière].[PF - Année Financière].ALLMEMBERS * 
[Période Financière].[PF - Trimestre No].[PF - Trimestre No].ALLMEMBERS * 
[Période Financière].[PF - Période Financière No].[PF - Période Financière No].ALLMEMBERS * 
[Période Financière].[PF - Date Fin Période].[PF - Date Fin Période].ALLMEMBERS ) } 
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( -{ [Période Financière].[PF - Année Financière - Trimestre].[PF - Année Financière].&[2014], [Période Financière].[PF - Année Financière - Trimestre].[PF - Année Financière].&[2013], [Période Financière].[PF - Année Financière - Trimestre].[PF - Année Financière].&[2012] } ) 
ON COLUMNS FROM ( SELECT ( STRTOSET(@[PériodeFinancièrePFAnnéeFinancière], CONSTRAINED) ) 
ON COLUMNS FROM [Historique Employés PerFin])) 
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

My apologies if my splitting of the code isn't the right way, I tried making it somehow logical being that the SSRS simply gives it as one big blurp.

Thanks!

Answer 1

After pouring over MDX stuff, I found the solution. Indeed, it has to be done in the Text Mode in the Query Designer and you have to use the Lag() function. So, here is what my DS looks like now:

WITH 
MEMBER [Measures].[Total_déb] 
AS ([PF - Date Fin Période].CurrentMember.Lag(12), [Measures].[Nb Matricules Actifs (DtDeb)])
MEMBER [Measures].[Départ_YTD]
AS 'SUM(
{[PF - Date Fin Période].CurrentMember.Lag(12):[PF - Date Fin Période].CurrentMember}, [Measures].[Nb Départs])'
MEMBER [Measures].[Départ_ret_YTD]
AS 'SUM(
{[PF - Date Fin Période].CurrentMember.Lag(12):[PF - Date Fin Période].CurrentMember}, ([Measures].[Nb Départs],[Départ].[Départ].&[112337]))'
SELECT NON EMPTY { [Measures].[Total_déb], [Measures].[Départ_YTD], [Measures].[Départ_ret_YTD], [Measures].[Nb Total Employés Actifs (DtFin)] } ON COLUMNS, NON EMPTY 
{ ([Période Financière].[PF - Année Financière].[PF - Année Financière].ALLMEMBERS * [Période Financière].[PF - Trimestre No].[PF - Trimestre No].ALLMEMBERS * [Période Financière].[PF - Période Financière No].[PF - Période Financière No].ALLMEMBERS * [Période Financière].[PF - Date Fin Période].[PF - Date Fin Période].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM ( SELECT ( -{ [Période Financière].[PF - Année Financière - Trimestre].[PF - Année Financière].&[2014], [Période Financière].[PF - Année Financière - Trimestre].[PF - Année Financière].&[2013], [Période Financière].[PF - Année Financière - Trimestre].[PF - Année Financière].&[2012] } ) ON COLUMNS 
FROM ( SELECT ( STRTOSET(@[PériodeFinancièrePFAnnéeFinancière], CONSTRAINED) ) ON COLUMNS FROM [Historique Employés PerFin])) 
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

And here's a screenshot of my DS now: