(Last Updated On: )

Dans cet article, je décris comment générer une table de dimension de dates dans Power Pivot / Power BI. La table générée aura la particularité de préciser les jours fériés et jours de repos (samedi, dimanche). L’utilisation d’une table de dimension de dates devient extrêmement importante lors de la visualisation des faits et des chiffres sur une période donnée.

A quoi sert une table des dates ?

Parfois, il est possible que nous n’ayons pas de valeurs de date continues dans l’ensemble de données sur lequel nous travaillons.

Par exemple, considérons les ventes d’un supermarché qui reste fermé le week-end et jours fériés. Dans un tel cas, il n’y aura des ventes que pour les jours de la semaine et aucune vente ne se produira le week-end ou les jours fériés. Si nous essayons de travailler sur les données d’un tel magasin, dans la source de données, nous n’aurons aucune donnée pour les jours où les ventes n’ont pas été effectuées. Cependant, pour faire des calculs basés sur le temps, il est nécessaire d’avoir toutes les dates en continu dans la base de données pour agréger correctement les résultats.

Ainsi, pour résoudre ce problème, nous pouvons introduire un concept de table de dimension de dates dans Power Pivot, dans lequel nous pré-remplirons toutes les valeurs de date possibles sur quelques années, puis effectuerons une jointure avec la table des ventes sur le champs date.

Lors de la création des visualisations, nous pourrons prendre les valeurs de dates de la table de dimension de dates et les valeurs de ventes de la table de ventes. De cette façon, nous aurons une idée claire des jours où il y a eu des ventes et quels étaient les jours sans vente.

Qu’est-ce qu’une table de dates ?

Une table de dates est une table qui contient un enregistrement par jour, ni plus, ni moins ! En fonction de la période utilisée dans l’entreprise, vous pouvez définir le début et la fin de la table de dates. Par exemple, votre table de dates peut commencer le 01/01/2016 et finir le 31/12/2029. Pour chaque année, vous aurez normalement 365 enregistrements (un enregistrement par an), sauf les années bissextiles avec 366 enregistrements.

Voici un exemple de table de dates :

Les jours fériés

Dans la plupart des entreprises, de nos jours, les jours fériés ou les informations sur les événements spéciaux constituent un aspect important du reporting et de l’analyse. Certaines analyses comme :

• Les ventes à Pâques ont-elles été meilleures cette année que l’année dernière ? (vous ne voulez pas calculer Pâques bien sûr)
• Quel est le chiffre d’affaires des jours en semaine par rapport aux week-end ?
• etc.

💡 J’ai montré dans un autre article comment calculer les jours fériés automatiquement dans Excel en fonction de l’année considérée.

Vous pouvez également avoir des champs dans la table des dates qui mentionnent si un jour est spécial (jour d’ouverture, jour des soldes…).

Votre table de dimension de dates peut être considérablement améliorée grâce aux informations fournies par ces champs.

Créer la table des dates

Il existe deux façons de créer la table de dimension des dates :

• Générer une table des dates dans Power Query
• Générer une table des dates dans Power Pivot

Nous allons utiliser la première façon car elle permet d’ajouter des informations dans la table plus facilement.

Commençons par créer deux requêtes dates dans l’éditeur Power Query : une requête date de début et une date de fin. Donc, nous allons commencer une nouvelle requête vide dans Power Query :

Dans la barre de formules, on peut entrer directement la date : 01/01/2016

On renomme ensuite la requête en DateDebut. Ensuite, on créé la date de fin de la même manière, qu’on renomme en DateFin.

💡 Dans le fichier exemple en téléchargement, je récupère les dates de début et de fin directement depuis une cellule nommée dans Excel. Ceci est possible en insérant la formule suivante à la place, où Name = “Cellule Nommée” :

= Excel.CurrentWorkbook(){[Name="DateDebut"]}[Content]

Maintenant, nous allons créer une nouvelle requête pour la générer la table des dates entre la date de début et la date de fin.

Nous allons dans l’éditeur avancé pour copier le code de génération de la table ci-dessous :

let

// Dates de début et fin
Date_debut = DateDebut,
Date_fin = DateFin,

// Calcul durée en jours
Période = Duration.Days(Date_fin - Date_debut) + 1,

// Création de la liste de dates
MyDate = List.Dates(Date_debut, Période, #duration(1,0,0,0)),

// Convertir en Table
Convert = Table.FromList(MyDate , Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Rename = Table.RenameColumns(Convert,{{"Column1", "Date"}}),
Type = Table.TransformColumnTypes(Rename,{{"Date", type date}}),

// Ajout des solonnes temporelles
AddAnnee = Table.AddColumn(Type, "Année", each Date.Year([Date]), Int64.Type),
AddExercice = Table.AddColumn(AddAnnee, "Exercice", each Text.From([Année]), type text),
AddMois = Table.AddColumn(AddExercice, "Mois", each Date.Month([Date]), Int64.Type),
MoisTexte = Table.AddColumn(AddMois, "MoisTexte", each Date.MonthName([Date]),type text),
PeriodeNum = Table.AddColumn(MoisTexte, "PeriodeNum", each Text.From([Année]) & Text.PadStart(Text.From([Mois]),2,"0"),type text),
AddPeriode = Table.AddColumn(PeriodeNum, "Période", each #date([Année],[Mois],1),type date),
AddTrimestre = Table.AddColumn(AddPeriode, "Trimestre", each Date.QuarterOfYear([Date]), Int64.Type),
AddSemaine = Table.AddColumn(AddTrimestre, "Semaine de l'année", each Date.WeekOfYear([Date]), Int64.Type),
AddJourSem = Table.AddColumn(AddSemaine, "Jour de la semaine", each Date.DayOfWeek([Date]), Int64.Type)

in

AddJourSem

On récupère tout d’abord les valeurs des requêtes DateDebut et DateFin créées précédemment dans des variables :

// Dates de début et fin 

Date_debut = DateDebut, 

Date_fin = DateFin,

La ligne suivante Période = Duration.Days(Date_fin - Date_debut) + 1 permet de calculer le nombre de jours entre la date de début et la date de fin.

Ensuite la ligne  MyDate = List.Dates(Date_debut, Période, #duration(1,0,0,0)),   créé la liste des dates entre les dates de début et de fin, en spécifiant la date de début, la durée et l’unité de la durée, #duration(1,0,0,0) signifiant l’unité Jour.

Les lignes suivantes permettent de convertir la liste en table et d’ajouter un certain nombre de champs utiles (Année, Trimestre, Jour de la semaine, etc.)

On sort ensuite de l’éditeur avancé en cliquant sur OK. La table des dates est générée et parfaitement fonctionnelle.

Ajouter les jours de repos à la table des dates

Pour ajouter les jours de repos, ici le week-end, on va tout d’abord créer une table de saisie dans Excel des jours de repos. Cette table prendra cette forme :

Jour semaine	Id	Repos
Lundi	0
Mardi	1
Mercredi	2
Jeudi	3
Vendredi	4
Samedi	5	X
Dimanche	6	X

Ensuite, on charge cette table dans PowerQuery :

On filtre la date sur la colonne Repos pour ne garder que les jours de repos sélectionnés avec un “X”, ici Samedi et Dimanche.

Enfin, on va simplement joindre notre table des dates avec cette table afin de récupérer les jours de repos. Pour cela, on sélectionne la table des dates dans la liste des requêtes, puis on va dans le menu Accueil -> Fusionner des requêtes, et on fait correspondre la colonne Jour Semaine de la table des dates avec la colonne Id de la table des jours de Repos :

(Veillez à ce que les numéros des jours de la semaine correspondent parfaitement entre les 2 tables)

Après avoir fusionné les deux requêtes, vous verrez un nouveau champs dans la table de dates, qui contient une table dans chaque cellule. Vous pouvez facilement développer cette cellule en cliquant sur le bouton Développer dans le coin supérieur droit de cette colonne.

Puis on sélectionne le champs Repos et on valide. Notre table des dates contient désormais l’information sur les jours de repos pour chaque date.

Ajouter les jours fériés à la table des dates

De la même manière que pour les jours de repos, on va charger une table contenant les jours fériés sur toute la période considérée, que l’on va ensuite joindre à la table des dates, pour ajouter l’information sur les jours fériés.

Pour obtenir la liste des jours fériés sur la période considérée, vous pouvez utiliser les formules dans mon précédent article, et copier la liste des jours fériés de chaque année dans une table Excel.

Puis en développant de la même manière la colonne créée lors de la fusion, on récupère les champs de la table des jours fériés pour les insérer dans la table des dates. Vous remarquerez que lorsqu’une date est un jour férié, la colonne récupérée aura la valeur du jour férié, et lorsque la date n’est pas un jour férié, elle aura la valeur null :

Requête complète

Voici le code complet de la requête Power Query pour générer la table des dates avec les jours fériés et les jours de repos. Elle est reliée à 2 autres requêtes listées ci-après.

A noter que j’ai ajouté une colonne “Jour Ouvré”, qui contient la valeur 1 lorsque la date est un jour travaillé et 0 si c’est un jour férié ou de repos.

Requête DimDate

let

// Dates de début et fin
Date_debut = DateDebut,
Date_fin = DateFin,

// Calcul durée en jours
Période = Duration.Days(Date_fin - Date_debut) + 1,

// Création de la liste de dates
MyDate = List.Dates(Date_debut, Période, #duration(1,0,0,0)),

// Convertir en Table
Convert = Table.FromList(MyDate , Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Rename = Table.RenameColumns(Convert,{{"Column1", "Date"}}),
Type = Table.TransformColumnTypes(Rename,{{"Date", type date}}),

// Ajouter des solonnes temporelles
AddAnnee = Table.AddColumn(Type, "Année", each Date.Year([Date]), Int64.Type),
AddExercice = Table.AddColumn(AddAnnee, "Exercice", each Text.From([Année]), type text),
AddMois = Table.AddColumn(AddExercice, "Mois", each Date.Month([Date]), Int64.Type),
MoisTexte = Table.AddColumn(AddMois, "MoisTexte", each Date.MonthName([Date]),type text),
PeriodeNum = Table.AddColumn(MoisTexte, "PeriodeNum", each Text.From([Année]) & Text.PadStart(Text.From([Mois]),2,"0"),type text),
AddPeriode = Table.AddColumn(PeriodeNum, "Période", each #date([Année],[Mois],1),type date),
AddTrimestre = Table.AddColumn(AddPeriode, "Trimestre", each Date.QuarterOfYear([Date]), Int64.Type),
AddSemaine = Table.AddColumn(AddTrimestre, "Semaine de l'année", each Date.WeekOfYear([Date]), Int64.Type),
AddJourSem = Table.AddColumn(AddSemaine, "Jour de la semaine", each Date.DayOfWeek([Date]), Int64.Type),

// Joindre jours repos hebdo
JoinJHebdo = Table.NestedJoin(AddJourSem, {"Jour de la semaine"}, TabJHebdo, {"Id"}, "TabJHebdo", JoinKind.LeftOuter),
DevJHebdo = Table.ExpandTableColumn(JoinJHebdo, "TabJHebdo", {"Jour semaine", "Repos"}, {"LibRepos","Repos"}),

// Joindre jours fériés et non travaillés
JoinJNO = Table.NestedJoin(DevJHebdo, {"Date"}, TabJNO, {"Date"}, "TabJNO", JoinKind.LeftOuter),
DevJNO = Table.ExpandTableColumn(JoinJNO, "TabJNO", {"Jour", "Date"}, {"LibJourNO0", "JourNO"}),

// Ajouter libellés jours
AddJourOuvre = Table.AddColumn(DevJNO, "Jour Ouvré", each if [Repos] <> null or [JourNO] <> null then 0 else 1, Int64.Type),
RemplaceNull = Table.ReplaceValue(AddJourOuvre,null,"",Replacer.ReplaceValue,{"LibRepos", "LibJourNO0"}),
AddLibelleJourNO = Table.CombineColumns(RemplaceNull,{"LibRepos", "LibJourNO0"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"LibJourNO"),
SupprEsp = Table.TransformColumns(AddLibelleJourNO,{{"LibJourNO", Text.Trim, type text}}),
SupprCol = Table.RemoveColumns(SupprEsp,{"Repos", "JourNO"}),

// Trier
Tri = Table.Sort(SupprCol,{{"Date", Order.Ascending}})
in
Tri

Requête TabJHebdo

let
Source = Excel.CurrentWorkbook(){[Name="TabJHebdo"]}[Content],
Filtre = Table.SelectRows(Source, each [Repos] <> null and [Repos] <> "")
in
Filtre

Requête TabJNO

let
Source = Excel.CurrentWorkbook(){[Name="TabJNO"]}[Content],
#"Lignes filtrées" = Table.SelectRows(Source, each [Jour] <> null and [Jour] <> ""),
#"Type modifié" = Table.TransformColumnTypes(#"Lignes filtrées",{{"Date", type date}})
in
#"Type modifié"

Utilisation de la table des dates dans le modèle de données Power Pivot

Une fois la table des dates générée, nous la chargeons dans le modèle de données du classeur Excel :

Nous avons également chargé des données des ventes dans le modèle de données.

Il nous reste à créer une relation entre la table des ventes et la table de dimension des dates pour pouvoir bénéficier de la puissance des tables de dates dans l’analyse. Pour cela, on va dans le Menu Power Pivot d’Excel, puis on clique sur Gérer.

Ensuite, le plus simple est de se mettre en vue de Diagramme en cliquant sur cet icône :

Enfin, on sélectionne le champs Date de la table des ventes et on vient le glisser sur le champs Date de la table des dates. Cela créé une relation entre les tables sur les dates et permet désormais de filtrer la table des ventes avec n’importe quel champs sélectionné de la table des dates.

Nous avons au final un modèle de données simple et fonctionnel, constitué d’une table de dimension (dates) et d’une tables des faits (les ventes).

Pour utiliser ce modèle de données, on retourne dans Excel et on insère un Tableau Croisé Dynamique ou un Graphique Croisé Dynamique.

Au moment de la sélection de la source du TCD, on veille bien à choisir l’option “Utiliser le modèle de données de ce classeur” :

Ensuite , dans la liste des champs du TCD  on va sélectionner le champs Jour Ouvré de notre table des dates, et on le glisse dans la zone Lignes du TCD.

Puis on sélectionne le champs de valeurs des Quantités vendues de la table des ventes et on le glisse dans la zone Valeurs du TCD.

Voici le résultat obtenu avec un Graphique Croisé Dynamique utilisant les mêmes champs.

Nous sommes désormais capable d’analyser les données des ventes sur un nouvel axe d’analyse Jour Ouvré qui n’existait pas à la base dans nos données !

A noter que pour ajouter le filtre chronologique de sélection des années, j’ai sélectionné le champs date de notre Table des dates. Ce type de filtre couplé à une table des dates est particulièrement puissant, car il vous permet de filtrer toutes les tables de votre modèle qui sont reliées à la table des dates en une seule opération.

📥TELECHARGER LE FICHIER D’EXEMPLE

Téléchargez le classeur Excel pour ce tutoriel. Seule votre adresse e-mail vous sera demandée dans la prochaine étape. Vous recevrez immédiatement le lien de téléchargement par e-mail.

Conclusion

Dans cet article, nous avons vu comment implémenter une table de dimension de dates dans un modèle de données Power Pivot et l’utiliser dans une analyse Excel à l’aide d’un tableau croisé dynamique. Nous avons vu comment ajouter les jours fériés à la table et visualiser nos données des ventes selon si la date est un jour ouvré ou non.

L’utilisation d’une table de dimension de dates est importante, en particulier pour des calculs basés sur le temps. Il est également utile de comprendre quelles ont été les périodes avec les meilleures ventes et les périodes où il n’y a pas eu de vente du tout.

En synthèse, l’utilisation de tables de dimension de dates ajoute un avantage important pour analyser de manière plus détaillée un ensemble de données temporelles.

En savoir plus

Enfin, si vous souhaitez apprendre une méthode rapide et efficace pour collecter et analyser vos données dans Excel, et créer des rapports dynamiques calculant des variations mensuelles / trimestrielles / annuelles,  des Year-to-Date, des cumuls glissants, etc. AUTOMATIQUEMENT, alors voici un programme 100% en ligne spécialement conçu pour découvrir et maîtriser les Power Tools de Microsoft facilement :