Calculating YoY (Year-over-Year) in DAX and SQL (2024)

Introduction

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

Contexte

Pour lesbesoins de la cause, nous allons utiliser la base de données de démonstration AdventureWorks2022de Microsoft. Cette base de données est un excellent outil pour pratiquer ettester 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èreapproche : via DAX

Le DAX(Data Analysis Expressions) est un langage de formule utilisé dans Power BIpour créer des calculs personnalisés. Dans cette première approche, nous allonsdémontrer comment transformer les données directement dans Power BI à partird'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 basede 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 dela 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.

Visualiserles données dans Power BI

Après avoirpréparé nos données avec les colonnes Year, Month, et YoY.Total, il est tempsde 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 vousdonnera 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, quese passe-t-il avec 2013 ?

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

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

Comment yremédier ?

Pourrésoudre ce problème, nous devons modifier notre méthode de jointure pourinclure toutes les données, même celles qui pourraient manquer dans l'une desanné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 = IfYear equalsnull 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. RemplacerYear 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 notreapproche, nous avons choisi d'inclure les mois dans nos calculs du YoY.Pourquoi cela ? Inclure les mois permet d'obtenir une granularité plus finedans l'analyse des données, ce qui est crucial pour les entreprises qui ont desvariations saisonnières significatives. En analysant les résultats mois parmois, il devient possible de détecter des tendances spécifiques à certainespé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 sontincomplètes pour certains mois, comme c'était le cas pour 2013, cela peutfausser les comparaisons d'une année sur l'autre.

Le problèmeavec 2013 vs 2014

Dans notreexemple, nous avons remarqué une anomalie en comparant les résultats de 2013 et2014. 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éesaprès juin 2014, ce qui signifie que les mois de juillet à décembre 2013n'étaient pas compilés. En conséquence, la comparaison pour 2014 était biaiséepar ces données manquantes.

Comment visualiser par mois

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

Une fois que les données sont correctementaligné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.

Pourquoipas les trimestres ?

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

Deuxième approche : SQL (MSSQL)

Si vousavez 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 approchepeut considérablement améliorer les performances, surtout lorsque voustravaillez avec de grands volumes de données.

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

Avec LAG (versions supportées)

Pour lesversions de SQL Server 2012 et ultérieures, vous pouvez utiliser la fonctionLAG comme suit :

Sans LAG(versions antérieures)

Pour lesversions antérieures à SQL Server 2012, voici une méthode alternative pourcalculer le YoY :

Analysedes performances

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

Comme onpeut le voir dans les résultats, sans la fonction LAG, le temps et lesressources nécessaires pour exécuter la requête sont considérablementaugmenté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 entermes de performance.

Conclusion

Dans cetarticle, nous avons exploré deux méthodes pour calculer le YoY (d'une année surl'autre) dans Power BI et SQL Server. Nous avons vu comment DAX dans Power BIoffre une flexibilité pour les sources de données fixes, et comment SQL, avecou sans la fonction LAG, permet de transformer les données en amont pouraméliorer les performances.

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

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