Avez-vous déjà voulu comparer les ventes avec celles du mois précédent, calculer des totaux cumulés ou même une moyenne mensuelle mobile ? Si la réponse est oui, vous avez besoin pour cela d’utiliser une table de dates.

La plupart du temps, une analyse de données porte sur différentes périodes, l'identification des tendances dans le temps ou l'évaluation de divers indicateurs en fonction du moment où des événements spécifiques se sont produits.

Vous pouvez vous débrouiller sans connaître les détails sur les clients, les fournisseurs ou les produits. mais votre analyse sera très difficile, voir impossible sans prendre en compte la dimension Temps.

Dans un modèle de données, la présence d’une table des dates garantit de pouvoir effectuer des analyses historiques complètes et précises. La table des dates est certainement la dimension la plus importante de tout modèle de données.

Pourquoi créer une table de dates ?

Lorsque vous chargez vos données, Power Pivot ou Power BI vont créer par défaut une hiérarchie de dates. Cette hiérarchie est composée de l'année, du trimestre, du mois et de la date afin que vous puissiez regrouper les données. Et, ils vont le faire pour chaque date présente dans votre table de faits.

Quel est le problème ?

Que se passe-t-il si je veux permettre à mes utilisateurs de regrouper les données selon une autre hiérarchie ? Par semaine ? Exercice fiscal ? Jour de la semaine ? Rien de cela n’est possible.

Que se passe-t-il si je veux comparer, par exemple, les dates de livraison souhaitées et celles du Bon de Livraison ? A nouveau, les hiérarchies automatiques de dates ne le permettent pas.

Enfin, les fonctions de Time Intelligence du langage DAX, notamment pour comparer la période actuelle avec l’année précédente, repose sur la présence d’une table de dates.

Pourquoi Microsoft propose-t-il cette possibilité ?

Excel et Power BI sont proposés par Microsoft comme des outils de BI en libre-service. Cette fonctionnalité de dimension temps automatique permet à l’utilisateur de ne pas se préoccuper des relations entre les tables, ni du modèle de données sous-jacent à son analyse.

Alors, même si dans 80% des cas, cette solution répondra à votre besoin, ne l'utilisez tout simplement pas. En créant votre propre table de dates, vous avez la garantie de pouvoir faire évoluer votre analyse comme bon vous semble.

Comment créer une table de dates dynamique ?

Il existe de nombreuses techniques pour créer une table de dates. Vous pouvez, tout simplement, récupérer la table des dates de votre organisation. Si elle existe, c’est certainement la solution à privilégier.

Vous pouvez aussi créer la table de dates directement dans Power Pivot ou Power BI avec les fonctions DAX CALENDAR et CALENDARAUTO. Il vous reste ensuite à ajouter les colonnes calculées pour l’année, le mois, …

Cet article décrit comment créer une table de dates dynamique à l'aide de Power Query, en quelques étapes, sans écrire une ligne de code (ou presque).

Il y a plus rapide, plus simple et certainement plus efficace. Mais si vous débutez avec Power Query, vous allez apprendre les bases de la manipulation des dates. Il sera temps ensuite de regarder le code généré pour optimiser et enrichir votre requête.

En partant de votre table de faits (vos ventes, vos livraisons, votre planning …), nous allons créer une table de dates avec un enregistrement par jour. Il sera ensuite très facile d’ajouter d’autres colonnes qui affichent les attributs de la date (année, mois, jour, jour férié, jour ouvrable, …).

La table de dates débute le premier jour de l’année de la première date de votre table de faits (vos ventes, vos livraisons, votre planning …). Elle se termine le dernier jour de l’année de la dernière date de votre table de faits. Elle contient l’ensemble des jours entre ces 2 dates.

A chaque mise à jour de la table de fait (ventes, inventaire, planning), la table date est recalculée et contient ainsi les années complètes correspondantes.

Étape 1 : Créer la table des dates à partir de la table de fait

La première étape consiste à charger les données de notre table de faits dans Power Query. Dans cet exemple, nous utiliserons un fichier des ventes contenant trois colonnes : dates, articles et quantité.

Ensuite, il nous faut récupérer les dates de notre table de fait qui nous serviront de base pour créer notre table des dates.

Clic droit sur la requête Feuil1, puis sélectionner Dupliquer.
Renommer la nouvelle requête : Date.
Clic droit sur l’entête de la colonne Date sales et sélectionner Supprimer les autres colonnes
Clic droit sur l’entête de la colonne Date sales et sélectionner Supprimer les doublons
Renommer la dernière étape appliquée : Base

Le résultat est une table Date avec une seule colonne, Sales dates, qui contient toutes les dates distinctes des ventes.

Étape 2 : Déterminer les dates de début et de fin

Nous voulons créer une table des dates dont les caractéristiques sont les suivantes :

Elle débute le premier jour de l’année de la première date de la table des ventes
Elle se termine le dernier jour de l’année de la dernière date de la table des ventes
Elle contient l’ensemble des jours entre ces 2 dates

Pour déterminer la date de début de notre calendrier, nous avons besoin de connaître la date de vente la plus ancienne et d’en déduire la date du premier jour de l’année.

Dans le menu déroulant de l’entête de colonne Date sales, sélectionner Filtres chronologiques, puis Est la plus ancienne
Sélectionner la colonne Date Sales. Dans l’onglet Ajouter une colonne, sélectionner Date et heure de début > Date, puis Année > Début d’année
Clic droit sur la cellule de la nouvelle colonne Début d’année et sélectionner Drill-down
Renommer la dernière étape appliquée : StartDate

Pour la date de fin, nous allons procéder de la même manière en repartant de la table avant transformation.

Clic droit sur la dernière étape appliquée et sélectionner Insérer l’étape après
Dans la barre de formule, remplacer = StartDate par = Base

Cette fois-ci, nous recherchons la date de vente la plus récente.

Dans le menu déroulant de l’entête de colonne Date sales, sélectionner Filtres chronologiques, puis Est la plus récente
Sélectionner la colonne Date Sales. Dans l’onglet Ajouter une colonne, sélectionner Date et heure de début > Date, puis Année > Fin d’année
Clic droit sur la cellule de la nouvelle colonne Fin d’année et sélectionner Drill-down
Renommer la dernière étape appliquée : EndDate

Étape 3 : Calculer les dates du calendrier

Je vous avais promis de créer notre table de dates sans écrire une ligne de code (ou presque). Et bien, nous y sommes !

Voici la seule formule que nous allons utiliser. Il s’agit de la fonction List.Dates

Cette fonction retourne une liste de dates défini par trois arguments :

List.Dates(start as date, count as number, step as duration) as list

start as date : la date de début du calendrier
count as number : le nombre de dates de la liste
step as duration : l’incrément choisi entre chaque date de la list

La syntaxe de l’argument step est la suivante :
#duration(days as number, hours as number, minutes as number, seconds as number)

Appliqué à notre exemple, cela donne #duration(1,0,0,0) puisque nous voulons un incrément d’un jour entre chaque date.

Assez de théorie. Passons à la pratique :

Clic droit sur la dernière étape appliquée et sélectionner Insérer l’étape après
Dans la barre de formule, remplacer = StartDate par la formule suivante

= List.Dates(StartDate,Duration.Days(EndDate-StartDate),#duration(1,0,0,0))

Dans l’onglet Outils de liste > Transformer, cliquer sur Convertir > Vers la table puis dans la fenêtre, cliquez sur OK
Sélectionner la colonne Column1 et changer le type de données en Date
Renommer la colonne Column1 : Date

Étape 4 : Ajouter les colonnes d’attributs de date (année, mois, ...)

Maintenant que nous avons notre colonne de date, nous pouvons très facilement ajouter des colonnes supplémentaires qui vous permettront de regrouper vos données de ventes selon les périodes de votre choix : année, trimestre, mois, …

Par exemple, pour ajouter une colonne Année à la table des dates, rien de plus simple :

Sélectionner la colonne Date
Dans l’onglet Ajouter une colonne, sélectionner Date et heure de début > Date
Dans le menu contextuel, sélectionner Année > Année

Procéder de la même manière pour ajouter d’autres colonnes. Les possibilités offertes sont très importantes. Pour n’en citer que quelques-unes : Début d’année, Fin d’année, Mois, Nom du mois, Fin de mois, Début de mois, Nom du jour, …

Exploitez la puissance des fonctions de Time Intelligence

La création de notre table des dates est maintenant terminée. Cette table est dynamique. A chaque fois que vous mettrez à jour votre fichier des ventes, Power Query actualise la table des dates pour qu’elle ne contienne que les années complètes qui correspondent à vos ventes. Vous n’avez plus à vous en préoccupez.

Vous pouvez maintenant utiliser les fonctions de Time Intelligence du langage DAX, notamment pour comparer la période actuelle avec l’année précédente, dans vos tableaux de bord.

Une table de dates est peut-être la table la plus importante que vous ajouterez à tous vos modèles de données. Au sein d'une organisation, une table de dates doit être définie de manière cohérente.

Quelle que soit la technique que vous décidez d'utiliser, partagez votre table des dates avec tous les utilisateurs de votre organisation. Ainsi, chaque fois que quelqu'un crée un nouveau tableau de bord, il peut commencer avec une table de dates définie de manière cohérente, complète et à jour pour votre organisation.

Autres articles DATA SUPPLY CHAIN