Calendrier de référence dans Power BI
Une étape importante dans la phase de modélisation d’un projet Power BI est de créer une table de dates de référence liée à toutes les tables du projet contenant une colonne de dates. Ce calendrier sera composé de la succession de toutes les dates uniques entre la date la plus ancienne et la date la plus récente de toutes les colonnes dates présentes dans le projet grâce à la fonction DAX CALENDARAUTO().
Sans cela vous ne serez pas en mesure d’utiliser les fonctions DAX d’intelligence temporelle (Time Intelligence) qui permettent de manipuler les données en utilisant des périodes de temps (jours, mois, trimestres et années, entre autres), puis de générer et comparer des calculs de type Date To Date sur ces périodes, ce qui est la grande force de Power BI par rapport à Excel.
Le code de ce calendrier universel qui peut être utilisé avec tous vos projets est le suivant :
CALENDRIER = ADDCOLUMNS(
CALENDARAUTO() ,
« Année » , FORMAT([Date], »YYYY »),
« Rang Année », RANKX(CALENDARAUTO(), YEAR([Date]), , ASC, Dense),
« Année Semaine » , YEAR([Date]) & « – » & FORMAT(WEEKNUM([Date], 2), « 00 »),
« Rang Semaine », RANKX(CALENDARAUTO(), YEAR([Date]) & « – » & FORMAT(WEEKNUM([Date], 2), « 00 »), , ASC, Dense),
« Année Mois » , FORMAT([Date], »YYYY-MM »),
« Rang Mois » , RANKX(CALENDARAUTO(), FORMAT([Date], »YYYY-MM »), , ASC, Dense),
« Année Trimestre » , FORMAT([Date], »YYYY-TQ »),
« Rang Trimestre » , RANKX(CALENDARAUTO(), FORMAT([Date], »YYYY-TQ »), , ASC, Dense)
)
Il suffira alors d’aller dans l’onglet Modélisation de Power BI Desktop, de créer une Nouvelle table, de copier-coller le code ci-dessus, de valider puis de la Marquer comme table de dates.
Enfin, une fois la table vérifiée, il faudra aller dans la Vue de modèle de Power BI pour relier tous les champs dates des tables du projet à la colonne Date du CALENDRIER de référence ainsi créé.
Les colonnes commençant par « Année » vous permettront de regrouper facilement les données de votre modèle suivant la période correspondante dans tous vos visuels.
Les colonnes commençant par « Rang », qui sont numériques, à l’inverse des colonnes commençant par « Année », vous permettront la même chose avec la possibilité en plus d’effectuer des mesures comparatives entre différentes périodes avec un simple CALCULATE sans passer par des fonctions DAX complexes.
Exemple
Vous souhaitez connaître la valeur de la colonne CA de la table Ventes pour le même mois de l’année précédente. Il suffira de créer la mesure suivante :
CA Mois-12 = CALCULATE( SUM(Ventes[CA]) , CALENDRIER[Rang Mois] = MAX( CALENDRIER[Rang Mois] ) – 12 )
La fonction CALCULATE filtre la somme de la colonne CA en allant chercher sa valeur mensuelle 12 mois avant le mois en cours.
Cet exemple simple pourra être généralisé à tous vos besoins sur toutes vos périodes en changeant uniquement de Rang.
Vous pourrez aussi de cette façon unique calculer des variations en pourcentage entre périodes où il suffira alors de diviser les différentes mesures obtenues avec CALCULATE.
