Comment calculer YTD, MTD en MDX - PeriodsToDate

Un besoin récurrent dans les rapports de Business Intelligence est de comparer les mesures à fréquence journalière ou mensuelle (chiffre d’affaire à titre d’exemple) par rapport aux mesures cumulées jusqu’à une date donnée (jour ou mois). Pour cette raison, on utilise fréquemment les notions de YTD (Year-to-Date) et MTD (month-to-Date).

Plus souvent les mesures citées précédemment sont calculées dans le cube, d’autres mesures peuvent être rajoutées par ex. QTD (Qurter-to-Date) et WTD (Week-to-Date)

Donc, comment calcule-t-on ces mesures en MDX ? 

Pour ce faire, il existe des fonctions, à savoir :

1. YTD (member_expression) : est une fonction qui renvoie un ensemble de membres du même parent (sibling), cette liste contient en première position le premier frère et en dernière position le membre spécifié : member_expression.

Ex. La fonction utilisée toute seule :

YTD([Time].[CalendarMonth].[Month Id].&[201606]) renvoie la liste suivante :

January 2016

February 2016

March 2016

April 2016

May 2016

June 2016

WITH

  SET [Liste_Mois] AS

    YTD([Time].[CalendarMonth].[Month Description].&[June 2016])

SELECT

  {[Measures].[Amount] } ON COLUMNS

 ,[Liste_Mois] ON ROWS

FROM [Mon_Cube]

	Amount
January 2016	11500
February 2016	12340
March 2016	10000
April 2016	12780
May 2016	13423
June 2016	20000

WITH

  SET [Liste_Mois] AS

    YTD([Time].[CalendarMonth].[Month Description].&[June 2016])

MEMBER [Measures].[Amount YTD] AS

    sum

    (

      YTD([Time].[CalendarMonth].currentmember)

     ,[Measures].[Amount]

    )

SELECT

  {{[Measures].[Amount] ,[Measures].[Amount YTD] } ON COLUMNS

 ,[Liste_Mois] ON ROWS

FROM [Mon_Cube]

La deuxième colonne correspond simplement aux montants cumulés depuis Janvier à Juin 2016 (voir ci-dessous):

NB. On peut ne pas spécifier une expression à la fonction YTD, le résultat sera le même car la valeur par défaut sera le membre actuel de la dimension  de type Time : Time.currentmember

Colonne1	Amount	Amount YTD
January 2016	11500	11500
February 2016	12340	23840
March 2016	10000	33840
April 2016	12780	46620
May 2016	13423	60043
June 2016	20000	80043

2. MTD (member_expression) : renvoie la liste des enfants en partant du 1er jour du mois jusqu'au jour spécifié en paramètre (member_expression)
Ex.

MTD([Time].[CalendarMonth].[Date].[June 8, 2016] : renvoie la liste du 1er Juin au 8 juin.

WITH

  SET [Liste_Jours] AS

    MTD([[Time].[CalendarMonth].[Date].[June 8, 2016])

MEMBER [Measures].[Amount MTD] AS

    sum

    (

      MTD([Time].[CalendarMonth].[Date].currentmember)

     ,[Measures].[Amount]

    )

SELECT

  {{[Measures].[Amount] ,[Measures].[Amount MTD] } ON COLUMNS

 ,[Liste_Jours] ON ROWS

FROM [Mon_Cube]

Colonne1	Amount	Amount MTD
June 1,  2016	120	120
June 2,  2016	560	680
June 3,  2016	780	1460
June 4,  2016	1000	2460
June 5,  2016	6000	8460
June 6,  2016	7200	15660
June 7,  2016	3540	19200
June 8,  2016	2626	21826

2. PeriodsToDate(Level_Expression, [Member_Expression]):  est la version générique qui permet de renvoyer des membres quelque soit l'échelle temporelle.

L'exemple ci-dessous renvoie les 2 premiers mois du deuxième semestre de l'année 2016.

        PeriodsToDate(  

            [Date].[Calendar].[Calendar Semester],   

            [Date].[Calendar].[Month].[August 2016]  

        )