Moyenne Mobile avec Power Pivot

Moyenne Mobile  Power Pivot + Dax - Moyen

Tags : DATESINPERIOD; LASTDATE; CALCULATE; TRI PAR COLONNE; DISTINCTCOUNT

Temps de préparation : 15 minutes

Remerciements : Rob Collie, Kasper de Jonge

Définition : La moyenne glissante, ou moyenne mobile, est un type de moyenne statistique utilisée pour analyser des séries ordonnées de données, le plus souvent des séries temporelles, en supprimant les fluctuations transitoires de façon à en souligner les tendances à plus long terme. Cette moyenne est dite mobile parce qu'elle est recalculée de façon continue, en utilisant à chaque calcul un sous-ensemble d'éléments dans lequel un nouvel élément remplace le plus ancien ou s'ajoute au sous-ensemble.
Ce type de moyenne est utilisé généralement comme méthode de lissage de valeurs, en particulier dans le domaine financier pour l'analyse technique de cours boursiers.
Mathématiquement, toute moyenne mobile est un exemple de convolution. Physiquement, une moyenne mobile est un filtre passe-bas et possède ainsi un lien profond avec le traitement du signal.

Ingrédients :

- 1 table de fait avec un montant ou un nombre de vente

- 1 dimension temps 

Préparation de la recette :

On dispose d'un modèle de données :

 
Lorsque je trace une courbe de mes ventes par mois, je dois mettre en regard le total des unités vendues par mois.
Pour cela j'utilise ma dimension temps et une mesure calculée bien pratique que je vais mettre en œuvre dans ma table de fait soit :
Total vente := SUM([Qté vendue])
Je vérifie en faisant un tableau croisé dynamique simple :

Petit problème d'affichage, mes mois sont classés par ordre alphabétique.
Pour corriger, je vais dans PowerPivot et je fais (si ce n'est pas déjà fait) une colonne ordre mois :

Pour faire cette colonne OrdreMois dans PowerPivot, je peux créer une colonne calculée (j'ajoute une nouvelle colonne dans la table Temps et la formule sera :
=FORMAT([Date];"yyyymm")

 
Ensuite dans la table Temps, je classe les mois par OrdreMois, pour cela je vais dans le ruban Accueil de la fenêtre Power Pivot et je clique sur l'icône de tri par colonne :

Je trie donc mon Mois par l'ordre.

 
Le graphique croisé dynamique (GCD) correspondant avec le bon ordre :
 
Si je traduis la définition, on peut considérer que la Moyenne mobile sur 3 mois doit être comme suit :

Sachant que le premier et le second mois ne doivent pas casser l'impression de lissage de la moyenne.
Décomposons le calcul qui doit être fait :
On cherche à calculer la moyenne des sommes des quantités vendues dans la période de temps comprises entre le mois en cours et les 3 mois précédents en prenant en compte le nombre réel de mois écoulée pour le calcul de la moyenne.
Bonne nouvelle, le DaX dispose d'une fonction permettant de gérer un intervalle de temps, il s'agit de DATESINPERIOD, comme son nom l'indique, ça va nous permettre de récupérer une table de dates dont les valeurs sont comprises entre une date de début et un intervalle temporelle.
Soit :
DATESINPERIOD(la table de dates, Date de Début, Intervalle, Type d'intervalle)
Pour la date de début, on utilisera LASTDATE, dont le rôle est d'indiquer la dernière date du contexte de filtre sélectionné.
Soit :
LASTDATE(Dim_temps[Date]) dans le TCD au niveau du mois de janvier donnera le 31/01.
Pour calculer une somme de quantité dans un contexte différent de celui de la table entière, on utiliser la fonction CALCULATE, qui, comme son nom ne l'indique pas, va nous permettre de calculer quelque chose en fonction de filtre appliqués au contexte courant.
Soit :
CALCULATE(n'importe quel calcul, Filtre(s) de contexte)
Pour calculer cette formule, nous allons créer une première formule dont le rôle est de nous donner le cumul sur 3 mois, la formule de la mesure sera :

Le nom de la mesure : Somme 3 Mois
Le calcul : :=CALCULATE([Total Vente];

Le filtre : DATESINPERIOD(

La table et la colonne de date : Temps[date];

La dernière date du contexte : LASTDATE(Temps[date]);

L'intervalle : -3;

Le type d'intervalle : Month
))
Ce qui donne :
Somme 3 Mois:=CALCULATE([Total Vente];DATESINPERIOD(Temps[date];LASTDATE(Temps[date]);-3;Month))
Et dans PowerPivot :

Ensuite pour calculer la moyenne, on ne va pas se contenter de diviser par 3 mais on va diviser par le nombre de mois de la période de référence fournie par DATESINPERIOD et pour cela, on utilisera une autre fonctions bien pratique :
DISTINCTCOUNT, le rôle de cette fonction est de compter le nombre d'élément indiqué dans une table fournie.
Pour la table, nous fourniront le résultat de DATESINPERIOD.
Soit :
DISTINCTCOUNT(Mois, table de dates fournies)
La formule finale est donc :
Le nom de la mesure : MM 3 Mois
Le calcul : :=[Somme 3 Mois] / CALCULATE(
Le nombre de mois : DISTINCTCOUNT(Temps[Nom Mois]);
Le filtre : DATESINPERIOD(
La table et la colonne de date : Temps[date];
La dernière date du contexte : LASTDATE(Temps[date]);
L'intervalle : -3;
Le type d'intervalle : Month))
Ce qui donne : MM 3 Mois:=[Somme 3 Mois]/CALCULATE(DISTINCTCOUNT(Temps[Nom Mois]);DATESINPERIOD(Temps[date];LASTDATE(Temps[date]);-3;Month))

Notre GCD est maintenant :

Pour Aller plus loin :
J'ajoute à mon classeur Power Pivot une table liée contenant les sous-ensembles de moyennes mobiles possibles :

Je trie la colonne sous ensemble par ordre mois :

 Dans ma table DIM_MM je créer une mesure pour récupérer la variable sélectionnée dans le contexte :

Je modifie mes formules pour ajouter la variable MM Sélectionné soit :
Somme variable Mois:=CALCULATE([Total Vente];DATESINPERIOD(Temps[date];IF([MM selectionné]<0;LASTDATE(Temps[date]);FIRSTDATE(Temps[date]));[MM selectionné];Month))
Et
MM Variable Mois:=[Somme variable Mois]/CALCULATE(DISTINCTCOUNT(Temps[Nom Mois]);DATESINPERIOD(Temps[date];IF([MM selectionné]<0;LASTDATE(Temps[date]);FIRSTDATE(Temps[date]));[MM selectionné];Month))
Note : Le IF permet de déterminer si mon nombre de mois est positif ou négatif, dans le cas ou ma moyenne se fait sur un intervalle de x mois après ma date courante, il faut utilisé FIRSTDATE pour inclure le mois en cours au lieu de LASTDATE.
On refait le GCD avec notre nouvelle mesure et on y ajoute un sclicer pour la sélection du sous ensemble :