Bien utilisé, Excel est un outil incroyable pour l'analyse de données et la création d'outils de pilotage modernes et visuels. Notamment grâce à un outil aussi simple qu'efficace : les tableaux croisés dynamiques (TCD) !

‍

Cet outil no-code d'Excel permet d'analyser facilement des données, en quelques clics, sans avoir à écrire une seule formule ni une seule fonction. C'est à la fois l'outil le plus simple et le plus utile du tableur historique de Microsoft. 

‍

Mais les TCD, utilisés seuls, restent un outil limité lorsque les besoins se complexifient vraiment, et beaucoup d'utilisateurs et d'utilisatrices sont alors tentées de rebasculer vers l'approche par formules et fonctions. Notamment lorsque :

‍

• Il y a plusieurs tables sources (les factures et les objectifs par exemple)
• Les indicateurs à obtenir ne sont pas le nombre, la somme ou la moyenne
• Il faut faire de l'analyse temporelle (intelligence temporelle, time intelligence)

‍

‍

‍

‍

Pour permettre d'utiliser tout le potentiel des TCD, y compris dans les cas cités ci-dessus, il est important de les utiliser avec Power Pivot, l'un des outils du Modern Excel, qui permet de démultiplier les capacités d'analyses des TCD. 

‍

‍

Power Pivot dans Excel, c'est quoi ?

‍

Power Pivot est un outil intégré à Excel depuis 2010, qui permet de démultiplier les capacités d'analyse des TCD à travers 3 éléments principaux :

‍

• L'organisation des données en modèle relationnel (tables, relations...)
• L'analyse via des mesures (de "super" fonctions, en langage DAX)
• L'optimisation des performances pour les volumes de données importants

‍

‍

L'organisation des données en modèle relationnel

‍

Power Pivot permet d'organiser les différentes données en tables dans un modèle relationnel. Ces tables de données sont séparées en 2 catégories distinctes : les tables de faits et les tables de relation

‍

• ‍Les tables de faits correspondent aux éléments mesurables de vos données. Ce sont les chiffres et les dates, comme les montants de chiffre d'affaires, les dates des factures, les dates d'embauche, etc. Ces tables de faits ont souvent beaucoup de lignes (nommées "enregistrements"), qui représentent des évènements ou des transactions.
• ‍Les tables de dimensions correspondent aux éléments descriptifs de vos données. Ce sont souvent des textes, comme la ville, la région, le numéro du client, le type de charge, etc. Ces tables de dimensions ont en général relativement peu de lignes. Elles permettent de filtrer ou regrouper les enregistrements des tables de faits par catégorie.

‍

Dans un modèle relationnel, les tables de faits et de dimensions sont connectées entre elles via des relations. Cette architecture de modèle, souvent en "étoile", permet de mieux garantir la qualité des données lors de l'analyse, évitant ainsi les redondances et les erreurs.

‍

‍

L'analyse via des mesures (de "super fonctions", en langage DAX)

‍

Le cœur de l'analyse dans Power Pivot réside dans la création de mesures, qui sont des formules rédigées en langage DAX (Data Analysis Expressions), le langage de formule du Modern Excel. 

‍

Ces "super fonctions" permettent de calculer des agrégations dynamiques, des indicateurs de performance (KPI) et des analyses temporelles complexes, comme le calcul de la croissance d'une année sur l'autre ou des moyennes mobiles. Les mesures DAX sont utilisables dans les TCD. 

‍

‍

L'optimisation des performances pour les volumes de données importants

‍

Power Pivot intègre un moteur de données en mémoire appelé VertiPaq, conçu pour gérer des millions de lignes de données directement dans Excel sans ralentissement. Ce moteur utilise une compression massive basée sur le stockage en colonnes, ce qui optimise la vitesse de traitement pour les opérations d'agrégation et de filtrage. Grâce à cette technologie, Power Pivot surpasse largement les limites traditionnelles des feuilles de calcul et permet une analyse instantanée même sur des ensembles de données conséquents.

‍

‍

Dans cet article, nous ne nous attarderons par sur l'ensemble de Power Pivot et de ces principaux concepts, mais nous nous focalisons sur la création des tables de dates, qui sont des tables de dimensions (les éléments descriptifs de vos données) du modèle relationnel. 

‍

‍

Une table de dates, c'est quoi ?

‍

Les tables de dates sont une des tables de dimensions les plus importantes de quasiment tout modèle de données. Elles sont absolument pour permettre de faire de l'analyse de données lorsqu'il existe une variable temporelle aux données à analyser. 

‍

‍

Nécessité d'une table de dates

‍
L'analyse de données a souvent une dimension temporelle. Que ce soit pour vérifier une croissance, calculer un cumul, établir une comparaison période par période... le facteur temps est un facteur important - souvent essentiel - de toute analyse. 

‍

Pour que Power Pivot puisse interpréter et manipuler correctement le temps, il est impératif d'utiliser une table de dates dédiée. Cette table de dimension agit comme un dictionnaire de toutes les dates possibles, fournissant un contexte descriptif (jour, mois, année, trimestre, jours fériés, etc.) à chaque transaction contenue dans les tables de faits.

‍

Sans la table de dates, les fonctions temporelles sophistiquées du DAX ne peuvent pas fonctionner. Sans elle, la comparaison temporelle entre 2 tables de faits (du CA et des objectifs) serait aussi impossible. 

‍

‍

Principes d'une table de dates

‍
Une table de dates doit respecter 3 règles importantes :

• Elle doit ne présenter qu'une seule fois chaque date, sans doublon
• Elle doit représenter la totalité de l'amplitude des dates du modèle
• Elle doit ne pas être discontinue (les dates sur lesquelles il n'y a pas d'évènements ou de transaction doivent être indiquées quand même)

‍

‍

‍

Dans cette table, qui commence par la liste de toutes les dates nécessaires, chaque colonne représente un élément descriptif de la liste des dates, comme l'année, le trimestre, le numéro du mois, le nom du mois, le jour de la semaine... Les valeurs de ces colonnes permettent de catégoriser les dates dans des ensembles joints ou disjoints, comme les dates d'une année, d'un mois, ou les lundis. 

‍

La table de date - qui est une dimension essentielle de l'analyse dans un modèle relationnel - doit être marquée comme Table de Dates dans Power Pivot afin que le moteur VertiPaq reconnaisse formellement son rôle temporel dans le modèle.

‍

‍

Les fonctions DAX d'intelligence temporelle (time intelligence)

‍
Une fois la table de dates correctement configurée et liée aux tables de faits, elle débloque l'utilisation de l'intelligence temporelle en DAX.

‍

Ces fonctions comme TOTALYTD, SAMEPERIODLASTYEAR ou DATESBETWEEN deviennent disponibles. Elles permettent de créer des indicateurs de performance chronologiques précis, simplifiant l'analyse de tendances et la comparaison de performances sur différentes périodes sans avoir à écrire de formules complexes de gestion de dates.

‍

‍

‍

Création automatique dans Power Pivot

‍

Avantages : Méthode ultra simple en quelques clics
Inconvénients : Uniquement sur Excel, seulement 7 colonnes, personnalisations manuelles, mises à jour des dates manuelles, pas de gestion des calendriers spécifiques et jours fériés spécifiques

‍

Cette première méthode est la méthode la plus simple et surtout la plus rapide pour créer une table de dates dans Excel. Aucun code, ni DAX ni M. Aucune technique particulière. Juste quelques clics : terminé

‍

Une fois que le modèle de données dans Power Pivot contient au moins une table de faits avec des dates (par exemple la liste des factures), il est possible d'ajouter une table de dates générée automatiquement. 

‍

• Etape 1 : Aller dans Power Pivot

Si vous n’avez pas l’onglet Power Pivot dans les onglets de votre ruban, il est temps de l’ajouter ! (si vous êtes sur Windows, n’existe malheureusement pas sur Mac)

Voici la procédure :
Etape 1 : Aller dans Fichier >>> Options >>> Compléments
Etape 2 : Aller dans Gérer (tout en bas) >>> Sélectionner Compléments COM >>> Cliquer sur Atteindre
Etape 3 : Sélectionner Power Pivot >>> Cocher la case >>> Cliquer sur Ok

• Etape 2 : Aller dans l'onglet Conception du ruban Power Pivot
• Etape 3 : Aller dans Table de dates >>> Nouveau

‍

‍

La table de dates est générée automatiquement, bien que cette table soit figée et trop peu développée. Cette table peut être modifiée ou complétée dans le même onglet. Par exemple en ajustant la date de début ou la date de fin, ou en ajoutant une colonne pour le trimestre ou pour l'année scolaire ou fiscale. 

‍

Une fois la table de dates créée, il ne reste qu'à relier les dates des tables de faits aux dates de la table de dates pour accéder facilement à tous les bénéfices de cette relation tables de faits/tables de dimensions. 

‍

‍

Création en DAX (uniquement dans Power BI)

‍

Cette méthode est un bonus uniquement pour les utilisateurs et utilisatrices de Power BI. Elle ne peut pas être utilisée dans Excel, même avec Power Pivot, car il n'est pas possible de créer une table ex-nihilo dans Power Pivot. 

‍

Avantages : Colonnes personnalisées, mises à jour automatiques, code personnalisé réutilisable facile à copier-coller
Inconvénients : Uniquement sur Power BI, pas de gestion des calendriers spécifiques et jours fériés spécifiques, colonnes calculées contre-performantes

‍

La table de dates créée avec la première méthode contient toujours les mêmes 7 colonnes. Mais beaucoup d'analyses temporelles nécessitent plusieurs colonnes supplémentaires, comme l'année scolaire ou fiscale, le trimestre ou le numéro de semaine. Ces colonnes peuvent être ajoutées manuellement à chaque fois. Mais il est plus pratique d'avoir un code complet, à copier-coller, pour ajouter automatiquement une table de dates plus personnalisée et plus complète. 

‍

Avec la fonction CALENDARAUTO, pour créer une table de dates sans déterminer précisément les dates de début et de fin

^{Calendrier =
ADDCOLUMNS(
CALENDARAUTO() ,
"Année" , YEAR( [Date] ) ,
"Mois" , MONTH( [Date] ) ,
"Nom du mois" , FORMAT( [Date] , "MMMM") ,
"Année - Mois" , FORMAT( [Date] , "yyyy - MM") ,
"Trimestre" , "T" & QUARTER( [Date] ) ,
"Année - Trimestre" , Year( [Date] ) & " - T" & QUARTER( [Date] ) ,
"Semaine" , WEEKNUM( [Date] , 2 ) ,
"Année - Semaine" , YEAR( [Date] ) & " - " & WEEKNUM( [Date] , 2 ) ,
"Jour" , DAY( [Date] ) ,
"JourSem" , WEEKDAY( [Date] , 2 ) ,
"Nom du JourSem" , FORMAT( [Date] , "dddd" )
)}

‍

Avec la fonction CALENDAR, pour déterminer manuellement les dates de début et de fin

^{Calendrier =
ADDCOLUMNS(
CALENDAR( DATE(2024,1,1) , DATE(2025,12,31) ) ,
"Année" , YEAR( [Date] ) ,
"Mois" , MONTH( [Date] ) ,
"Nom du mois" , FORMAT( [Date] , "MMMM") ,
"Année - Mois" , FORMAT( [Date] , "yyyy - MM") ,
"Trimestre" , "T" & QUARTER( [Date] ) ,
"Année - Trimestre" , Year( [Date] ) & " - T" & QUARTER( [Date] ) ,
"Semaine" , WEEKNUM( [Date] , 2 ) ,
"Année - Semaine" , YEAR( [Date] ) & " - " & WEEKNUM( [Date] , 2 ) ,
"Jour" , DAY( [Date] ) ,
"JourSem" , WEEKDAY( [Date] , 2 ) ,
"Nom du JourSem" , FORMAT( [Date] , "dddd" )
)}

‍

Alternative avec la fonction CALENDAR (à modifier dans le code ci-dessus), pour déterminer les dates de début et de fin en fonction d'une colonne d'une autre table

^{CALENDAR( MINX( Factures , [Date] ) , MAXX( Factures , [Date] ) )}

‍

Cette méthode en DAX avec CALENDARAUTO ou CALENDAR est donc bien plus efficace que la première méthode, car il est facile d'ajuster les dates et les colonnes aux besoins... mais 1/ elle n'est pas disponible sur Excel et 2/ elle est encore perfectible. 

‍

‍

Création en M dans Power Query

‍

Avantages : Dans Excel & Power BI, colonnes personnalisées, mises à jour automatiques, code personnalisé réutilisable facile à copier-coller, gestion des calendriers spécifiques et jours fériés spécifiques, performances optimisées
Inconvénients : ?

‍

Dans cette troisième méthode, le code est aussi facilement réutilisable avec de simples copier-coller, mais au lieu que ce soit en DAX, c'est en M dans Power Query. Cette méthode fonctionne exactement de la même manière dans Excel et dans Power BI. 

‍

Mais surtout, cette méthode a l'avantage de pouvoir être encore améliorée, en intégrant des éléments qui ne peuvent pas être intégrés (facilement en langage DAX) et de rendre le classeur plus performant. 

‍

• Etape 1 : Aller dans l'onglet Données du ruban >>> Obtenir des données >>> A partir d'autres sources >>> Requête vide
• Etape 2 : Cliquer sur Editeur avancé
• Etape 3 : Coller le code suivant

Avec les dates de début et de fin à déterminer manuellement

^{let
   DateDebut = #date(2024, 1, 1), //Vous pouvez modifier manuellement cette date
   DateFin = #date(2025, 12, 31), //Vous pouvez modifier manuellement cette date
   NombreDeJours = Number.From(DateFin - DateDebut) + 1,
   ListeDates = List.Dates(DateDebut, NombreDeJours, #duration(1, 0, 0, 0)),
   TableDesDates = Table.FromList(ListeDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   RenommerDate = Table.RenameColumns(TableDesDates,{{"Column1", "Date"}}),
   TypeDate = Table.TransformColumnTypes(RenommerDate,{{"Date", type date}}),
   AjouterAnnee = Table.AddColumn(TypeDate, "Année", each Date.Year([Date]), Int64.Type),
   AjouterMois = Table.AddColumn(AjouterAnnee, "Mois", each Date.Month([Date]), Int64.Type),
   AjouterNomMois = Table.AddColumn(AjouterMois, "Nom du mois", each Date.MonthName([Date]), type text),
   AjouterCleTriMois = Table.AddColumn(AjouterNomMois, "Clé tri mois", each Date.ToText([Date], "yyyy-MM"), type text),
   AjouterTrimestre = Table.AddColumn(AjouterCleTriMois, "Trimestre", each "T" & Text.From(Date.QuarterOfYear([Date])), type text),
   AjouterSemaine = Table.AddColumn(AjouterTrimestre, "Semaine de l'année", each Text.From([Année]) & "-" & Text.PadStart(Text.From(Date.WeekOfYear([Date])), 2, "0"), type text),
   AjouterJour = Table.AddColumn(AjouterSemaine, "Jour du mois", each Date.Day([Date]), Int64.Type),
   AjouterJourSem = Table.AddColumn(AjouterJour, "JourSem", each Date.DayOfWeek([Date]) + 1, Int64.Type),
   AjouterNomJour = Table.AddColumn(AjouterJourSem, "Nom du jour", each Date.DayOfWeekName([Date]), type text)
in
   AjouterNomJour}

• Etape 4 : Renommer la requête Table de dates
• Etape 5 : Aller dans l'onglet Accueil du ruban Power Query >>> Charger dans
• Etape 6 : Sélectionnez Ne créer que la connexion et cocher la case Ajouter au modèle de données
• Etape 7 : Aller dans l'onglet Power Pivot du ruban >>> Gérer
• Etape 8 : Aller dans l'onglet Conception du ruban Power Pivot >>> Marquer comme table de dates >>> Sélectionner la colonne Date
• Etape 9 : Aller dans l'onglet Accueil du ruban Power Pivot >>> Trier par colonne >>> Sélectionner Nom du mois puis Mois >>> Cliquer sur Ok

‍

‍

‍

Une fois toutes ces étapes terminées, votre table de dates est créée, paramétrée et prête pour être mise en relation avec vos autres tables du modèle. 

‍

Avec les dates de début et de fin déterminées automatiquement en fonction d'une colonne d'une autre requête

^{let
   DateDebut = #date(Date.Year( List.Min( #Requête1[Date] ) ), 1, 1), //Vous pouvez indiquer la requête et la colonne à utiliser pour calculer cette date
   DateFin = #date(Date.Year( List.Min( #Requête1[Date] ) ), 12, 31), //Vous pouvez indiquer la requête et la colonne à utiliser pour calculer cette date
   NombreDeJours = Number.From(DateFin - DateDebut) + 1,
   ListeDates = List.Dates(DateDebut, NombreDeJours, #duration(1, 0, 0, 0)),
   TableDesDates = Table.FromList(ListeDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   RenommerDate = Table.RenameColumns(TableDesDates,{{"Column1", "Date"}}),
   TypeDate = Table.TransformColumnTypes(RenommerDate,{{"Date", type date}}),
   AjouterAnnee = Table.AddColumn(TypeDate, "Année", each Date.Year([Date]), Int64.Type),
   AjouterMois = Table.AddColumn(AjouterAnnee, "Mois", each Date.Month([Date]), Int64.Type),
   AjouterNomMois = Table.AddColumn(AjouterMois, "Nom du mois", each Date.MonthName([Date]), type text),
   AjouterCleTriMois = Table.AddColumn(AjouterNomMois, "Clé tri mois", each Date.ToText([Date], "yyyy-MM"), type text),
   AjouterTrimestre = Table.AddColumn(AjouterCleTriMois, "Trimestre", each "T" & Text.From(Date.QuarterOfYear([Date])), type text),
   AjouterSemaine = Table.AddColumn(AjouterTrimestre, "Semaine de l'année", each Text.From([Année]) & "-" & Text.PadStart(Text.From(Date.WeekOfYear([Date])), 2, "0"), type text),
   AjouterJour = Table.AddColumn(AjouterSemaine, "Jour du mois", each Date.Day([Date]), Int64.Type),
   AjouterJourSem = Table.AddColumn(AjouterJour, "JourSem", each Date.DayOfWeek([Date]) + 1, Int64.Type),
   AjouterNomJour = Table.AddColumn(AjouterJourSem, "Nom du jour", each Date.DayOfWeekName([Date]), type text)
in
   AjouterNomJour}

‍

Cette méthode en langage M, dans Power Query, offre de nombreux avantages, quoi qu'elle soit légèrement plus difficile à prendre en mains pour les débutants. De plus, les codes ci-dessus peuvent largement être améliorés pour prendre en compte les jours fériés, les calendriers scolaires ou fiscaux... car Power Query offre beaucoup plus de possibilités avancées dans la création de tables de dates que le langage DAX.

‍

‍

En définitive, dans le Modern Excel, l'intégration d'une table de dates dédiée n'est pas une option, mais une exigence fondamentale pour toute analyse un peu avancée des TCD et de l'intelligence temporelle. 

‍

Mais le choix entre la création de cette table dans Power Pivot ou Power Query correspond à un arbitrage entre la simplicité et l'hyper-personnalisation. 

‍

Quel que soit l'outil choisi, cette dimension temporelle structurée est la clé de voûte pour débloquer des analyses précises et fiables, transformant des données brutes en indicateurs stratégiques pertinents pour la prise de décision.