Calcul d'une année sur l'autre (YoY) avec DAX et SQL

Introduction

Aujourd'hui, pour mon premier article dans le vif du sujet, je vous présente les dessous d'une mesure couramment utilisée en intelligence d'affaires : le YoY, ou "Year-over-Year". Cette mesure est essentielle pour évaluer la performance d'une entreprise sur une base annuelle, en comparant les résultats d'une période donnée à ceux de la même période l'année précédente. Dans cet article, nous explorerons comment calculer le YoY à l'aide de DAX dans Power BI, puis nous verrons comment l'obtenir directement dans une base de données SQL.

Contexte

Pour les besoins de la cause, nous allons utiliser la base de données de démonstration AdventureWorks2022 de Microsoft. Cette base de données est un excellent outil pour pratiquer et tester les concepts que nous allons aborder. Si vous souhaitez suivre les étapes chez vous, vous pouvez télécharger et installer cette base de données à partir du site officiel deMicrosoft.

Première approche : via DAX

Le DAX (Data Analysis Expressions) est un langage de formule utilisé dans Power BI pour créer des calculs personnalisés. Dans cette première approche, nous allons démontrer comment transformer les données directement dans Power BI à partir d'une source fixe telle qu'un fichier CSV, Excel, ou une base de données Access. Pour cet exemple, nous nous connecterons à la table SalesOrderHeader de la base de données AdventureWorks2022.

Étapes

1. Ajouter une source de données SQL : Commencez par ajouter une source de données SQL dans Power BI et importez les colonnes OrderDate et TotalDue de la table SalesOrderHeader. Vous pouvez renommez la requête pour plus de clarté (“SalesOrderHeader”) .

2. Ajouter une colonne Année : Utilisez la colonne OrderDate pour créer une nouvelle colonne Year qui extrait l'année de la date de commande.

3. Ajouter une colonne Mois : De la même manière, créez une colonne Month qui extrait le mois de la date de commande.

4. Ajouter une colonne Année Précédente : Créez une colonne PreviousYear en soustrayant 1 de la valeur de Year.

5. Grouper les données : Utilisez la fonctionnalité "Group By" pour regrouper les données par Year et Month, en calculant la somme de TotalDue pour chaque groupe.

6. Fusionner : Effectuez un auto-jointure (self-join) en utilisant les colonnes PreviousYear=Year et Month=Month pour obtenir les données de l'année précédente.

Note : Vous pouvez renommez (« YoY » dans ce cas-ci) le préfix du résultat de la jointure de cette façon :

7. Développer : Développez la colonne YoY.Total pour afficher le total de Total de l'année précédente.

Visualiser les données dans Power BI

Après avoir préparé nos données avec les colonnes Year, Month, et YoY.Total, il est temps de visualiser ces informations dans Power BI.

Étapes

1. Ajouter une mesure :

• Utilisez la fonctionnalité "Quick Measure" de Power BI pour créer une mesure de type "Pourcentage de différence" (Percentage Difference). Cette mesure calculera la différence en pourcentage entre Total et YoY.Total.

2. Ajouter un visuel de type Table :

• Créez un visuel de type Table dans Power BI.
• Placez Year dans les lignes (Rows).
• Ajoutez les colonnes Total, YoY.Total, et la mesure Total % difference dans les valeurs (Values).

Cela vous donnera une vue claire des revenus annuels, des montants de l'année précédente, et des pourcentages de changement d'une année sur l'autre.

Mais, que se passe-t-il avec 2013 ?

En regardant de plus près les données, vous remarquerez peut-être quelque chose d'étrange : le montant YoY pour 2024 n'est pas le même que celui de 2013. Que se passe-t-il ?

Si nous examinons la table de données dans son ensemble, nous constatons qu'il n'y a aucune entrée après juin 2014. Cela signifie que les mois de juillet à décembre 2013 ne sont pas compilés dans nos calculs. Cela fausse évidemment la comparaison d'une année sur l'autre.

Comment y remédier ?

Pour résoudre ce problème, nous devons modifier notre méthode de jointure pour inclure toutes les données, même celles qui pourraient manquer dans l'une des années comparées.

Étapes de correction

1. Revenir à l'étape 6 :

• Modifiez le type de jointure (Join Kind) pour un Full Outer Join plutôt qu'une jointure par défaut (Left Outer). Cela permettra d'inclure toutes les paires Year-Month, même si elles sont manquantes dans l'une des années.

2. Garder YoY.Year et YoY.Month :

• Après l'expand de YoY.Total, assurez-vous de garder également YoY.Year et YoY.Month dans votre tableau.

3. Ajouter des colonnes pour fixer Year et Month :

• Ajoutez une colonne personnalisée pour comparer YoY.Year et Year, puis créez une colonne conditionnelle YearFixed qui contient la bonne valeur.

• • YoY.Year1 = YoY.Year + 1

• • YearFixed = If Year equals null then YoY.Year1, else Year

• Ajoutez également une colonne conditionnelle MonthFixed avec la logique suivante :

• • MonthFixed = if Month is null then YoY.Month else Month

4. Remplacer Year et Month :

• Remplacez les colonnes Year et Month dans votre tableau final par YearFixed et MonthFixed pour corriger les anomalies.

Pourquoi inclure les mois ?

Dans notre approche, nous avons choisi d'inclure les mois dans nos calculs du YoY. Pourquoi cela ? Inclure les mois permet d'obtenir une granularité plus fine dans l'analyse des données, ce qui est crucial pour les entreprises qui ont des variations saisonnières significatives. En analysant les résultats mois par mois, il devient possible de détecter des tendances spécifiques à certaines périodes de l'année, et non pas uniquement des tendances annuelles globales.

Cependant, cette approche introduit un défi particulier : lorsque les données sont incomplètes pour certains mois, comme c'était le cas pour 2013, cela peut fausser les comparaisons d'une année sur l'autre.

Le problème avec 2013 vs 2014

Dans notre exemple, nous avons remarqué une anomalie en comparant les résultats de 2013 et 2014. Le montant YoY pour 2024 n'était pas le même que celui calculé pour 2013. Cette divergence était due au fait que nos données ne contenaient pas d'entrées après juin 2014, ce qui signifie que les mois de juillet à décembre 2013 n'étaient pas compilés. En conséquence, la comparaison pour 2014 était biaisée par ces données manquantes.

Comment visualiser par mois

Pour éviter ce genre de problème, nous avons modifié notre approche en utilisant un Full Outer Join lors de la jointure des données, ce qui nous a permis de conserver tous les mois, même ceux sans données, et de remplir ces périodes manquantes de manière adéquate.

Une fois que les données sont correctement alignées, vous pouvez les visualiser par mois dans Power BI :

1. Créer un visuel de type Matrice :

• Placez YearFixed et MonthFixed dans les lignes (Rows).
• Ajoutez les colonnes TotalDue, YoY.TotalDue, et Percent Change dans les valeurs (Values).

Analysez les tendances mensuelles :

• Ce visuel vous permettra de voir les variations mois par mois, et d'identifier des tendances saisonnières, ce qui peut être crucial pour certaines analyses commerciales.

Pourquoi pas les trimestres ?

Nous aurions pu également inclure les trimestres dans notre analyse. Cependant, comme les années fiscales varient d'une entreprise à l'autre, cela aurait introduit une complexité supplémentaire. Nous y reviendrons dans notre prochain article, où nous verrons comment créer une fonction pour calculer les trimestres en fonction du début de l'année fiscale de votre entreprise.

Deuxième approche : SQL (MSSQL)

Si vous avez accès à exécuter des requêtes SQL directement sur votre base de données, il est souvent préférable de transformer vos données en amont. Cette approche peut considérablement améliorer les performances, surtout lorsque vous travaillez avec de grands volumes de données.

Tout dépendant de la version de votre serveur MSSQL, vous pourrez utiliser la fonction LAG, qui simplifie grandement le calcul des variations d'une année sur l'autre. Pour les versions antérieures, nous démontrerons également comment procéder avec une approche plus traditionnelle, et comment cela impacte les ressources du serveur.

Avec LAG (versions supportées)

Pour les versions de SQL Server 2012 et ultérieures, vous pouvez utiliser la fonction LAG comme suit :

SELECT     YEAR(OrderDate) AS Year,     SUM(TotalDue) AS CurrentYearRevenue,     LAG(SUM(TotalDue), 1) OVER (ORDER BY YEAR(OrderDate)) AS PreviousYearRevenue,     ((SUM(TotalDue) - LAG(SUM(TotalDue), 1) OVER (ORDER BY YEAR(OrderDate))) / LAG(SUM(TotalDue), 1) OVER (ORDER BY YEAR(OrderDate)) * 100) AS YoY_Percent_Change FROM Sales.SalesOrderHeader GROUP BY YEAR(OrderDate) ORDER BY Year;

Sans LAG (versions antérieures)

Pour les versions antérieures à SQL Server 2012, voici une méthode alternative pour calculer le YoY :

SELECT     CurrentYear.Year,     CurrentYear.Revenue AS CurrentYearRevenue,     PreviousYear.Revenue AS PreviousYearRevenue,     ((CurrentYear.Revenue - PreviousYear.Revenue) / PreviousYear.Revenue) * 100 AS YoY_Percent_Change FROM     (SELECT YEAR(OrderDate) AS Year, SUM(TotalDue) AS Revenue      FROM Sales.SalesOrderHeader      GROUP BY YEAR(OrderDate)) AS CurrentYear LEFT JOIN     (SELECT YEAR(OrderDate) + 1 AS Year, SUM(TotalDue) AS Revenue      FROM Sales.SalesOrderHeader      GROUP BY YEAR(OrderDate)) AS PreviousYear ON CurrentYear.Year = PreviousYear.Year ORDER BY CurrentYear.Year;

Analyse des performances

Maintenant, regardons la différence en termes de ressources et de temps d'exécution au niveau du serveur. Nous avons utilisé SQL Server Profiler pour capturer les détails d'exécution de ces deux requêtes.

Comme on peut le voir dans les résultats, sans la fonction LAG, le temps et les ressources nécessaires pour exécuter la requête sont considérablement augmentés. Imaginez l'impact sur un serveur lorsque le volume de données est élevé. Cette approche plus ancienne peut devenir un goulot d'étranglement en termes de performance.

Conclusion

Dans cet article, nous avons exploré deux méthodes pour calculer le YoY (d'une année sur l'autre) dans Power BI et SQL Server. Nous avons vu comment DAX dans Power BI offre une flexibilité pour les sources de données fixes, et comment SQL, avec ou sans la fonction LAG, permet de transformer les données en amont pour améliorer les performances.

Nous avons également discuté des avantages d'inclure des analyses par mois et comment surmonter les défis lorsque les données sont incomplètes. Enfin, bien que nous ayons choisi d'analyser les données par mois, il est important de noter que l'analyse par trimestre pourrait être tout aussi pertinente. Cela étant dit, les trimestres fiscaux varient d'une entreprise à l'autre, ce qui introduit une complexité supplémentaire. Mais ne vous inquiétez pas, nous reviendrons sur ce sujet dans notre prochain article, où je vous montrerai comment créer une fonction pour calculer les trimestres relativement au début de l'année fiscale de votre entreprise.

J'espère que cet article vous a fourni des insights utiles pour optimiser vos analyses et améliorer l'efficacité de vos calculs de KPI. N'hésitez pas à partager vos questions ou vos expériences en commentaire!