Calendrier de référence M 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 de début et la date de fin précisées dans le code.
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.
Pour créer cette table de dates dans Power Query vous devez suivre les étapes suivantes :
- A partir de l’éditeur Power Query créer une Requête vide à partir du bouton Accueil / Nouvelle source / Requête vide
- Renommer cette requête « Calendrier » ou par tout autre nom par un clic droit sur la requête puis Renommer
- Ouvrir l’Editeur avancé par un nouveau clic droit sur la requête puis Editeur avancé
- Effacer tout le code présent puis coller le code entier ci-dessous en précisant les dates de début (StartDate) et de fin (EndDate) en fonction des besoins de votre projet :
let
// Définir la plage de dates
StartDate = #date(2024, 1, 1),
EndDate = #date(2024, 12, 31),
Source = List.Dates(StartDate, Duration.Days(EndDate – StartDate) + 1, #duration(1, 0, 0, 0)),
TableDates = Table.FromList(Source, Splitter.SplitByNothing(), {« Date »}),
ChangedType = Table.TransformColumnTypes(TableDates, {{« Date », type date}}),
// Colonnes de base
AddYear = Table.AddColumn(ChangedType, « Année », each Date.Year([Date]), Int64.Type),
AddQuarter = Table.AddColumn(AddYear, « Trimestre », each « T » & Text.From(Date.QuarterOfYear([Date])), type text),
AddYearQuarter = Table.AddColumn(AddQuarter, « Année Trimestre », each Text.From(Date.Year([Date])) & « -T » & Text.From(Date.QuarterOfYear([Date])), type text),
AddMonthNumber = Table.AddColumn(AddYearQuarter, « Mois Chiffre », each Text.PadStart(Text.From(Date.Month([Date])), 2, « 0 »), type text),
AddMonthName = Table.AddColumn(AddMonthNumber, « Mois Lettre », each Date.ToText([Date], « MMMM »), type text),
AddYearMonth = Table.AddColumn(AddMonthName, « Année Mois », each Date.ToText([Date], « yyyy-MM »), type text),
AddWeek = Table.AddColumn(AddYearMonth, « Semaine », each Text.PadStart(Text.From(Date.WeekOfYear([Date], Day.Monday)), 2, « 0 »), type text),
AddYearWeek = Table.AddColumn(AddWeek, « Année Semaine », each Text.From(Date.Year([Date])) & « – » & Text.PadStart(Text.From(Date.WeekOfYear([Date], Day.Monday)), 2, « 0 »), type text),
// Rang Année
DistinctYears = Table.Distinct(Table.SelectColumns(AddYearWeek, {« Année »})),
SortedYears = Table.Sort(DistinctYears, {{« Année », Order.Ascending}}),
AddYearRank = Table.AddIndexColumn(SortedYears, « Rang Année », 1, 1, Int64.Type),
MergedYearRank = Table.NestedJoin(AddYearWeek, {« Année »}, AddYearRank, {« Année »}, « YearRank », JoinKind.LeftOuter),
ExpandedYearRank = Table.ExpandTableColumn(MergedYearRank, « YearRank », {« Rang Année »}),
// Rang Trimestre
DistinctQuarters = Table.Distinct(Table.SelectColumns(ExpandedYearRank, {« Année Trimestre »})),
SortedQuarters = Table.Sort(DistinctQuarters, {{« Année Trimestre », Order.Ascending}}),
AddQuarterRank = Table.AddIndexColumn(SortedQuarters, « Rang Trimestre », 1, 1, Int64.Type),
MergedQuarterRank = Table.NestedJoin(ExpandedYearRank, {« Année Trimestre »}, AddQuarterRank, {« Année Trimestre »}, « QuarterRank », JoinKind.LeftOuter),
ExpandedQuarterRank = Table.ExpandTableColumn(MergedQuarterRank, « QuarterRank », {« Rang Trimestre »}),
// Rang Mois
DistinctMonths = Table.Distinct(Table.SelectColumns(ExpandedQuarterRank, {« Année Mois »})),
SortedMonths = Table.Sort(DistinctMonths, {{« Année Mois », Order.Ascending}}),
AddMonthRank = Table.AddIndexColumn(SortedMonths, « Rang Mois », 1, 1, Int64.Type),
MergedMonthRank = Table.NestedJoin(ExpandedQuarterRank, {« Année Mois »}, AddMonthRank, {« Année Mois »}, « MonthRank », JoinKind.LeftOuter),
ExpandedMonthRank = Table.ExpandTableColumn(MergedMonthRank, « MonthRank », {« Rang Mois »}),
// Rang Semaine
DistinctWeeks = Table.Distinct(Table.SelectColumns(ExpandedMonthRank, {« Année Semaine »})),
SortedWeeks = Table.Sort(DistinctWeeks, {{« Année Semaine », Order.Ascending}}),
AddWeekRank = Table.AddIndexColumn(SortedWeeks, « Rang Semaine », 1, 1, Int64.Type),
MergedWeekRank = Table.NestedJoin(ExpandedMonthRank, {« Année Semaine »}, AddWeekRank, {« Année Semaine »}, « WeekRank », JoinKind.LeftOuter),
ExpandedWeekRank = Table.ExpandTableColumn(MergedWeekRank, « WeekRank », {« Rang Semaine »}),
# »Lignes triées » = Table.Sort(ExpandedWeekRank,{{« Date », Order.Ascending}})
in
# »Lignes triées »
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.
Cette procédure est possible aussi bien dans Power BI Desktop que dans le service Power BI Fabric.
Voir aussi Calendrier de référence DAX dans Power BI.
