Créer une table de dates
Quand il s’agit de créer des rapports dans Power BI, les entreprises ont ceci en commun qu’elles basent leurs calculs sur des valeurs de dates et de temps. Elles souhaitent connaître l’évolution de leur activité au fil des mois, trimestres, années fiscales, etc. Pour cette raison, il est essentiel que ces valeurs de temps soient correctement mises en forme. Power BI détecte automatiquement les colonnes et les tables de dates. Or, dans certains cas, il peut être nécessaire de prendre des mesures supplémentaires pour adapter le format des dates aux besoins de l’entreprise.
Par exemple, supposons que vous élaborez des rapports pour l’équipe commerciale de votre entreprise. La base de données contient des tables pour les ventes, les commandes, les produits, etc. Vous remarquez que la plupart de ces tables, dont les tables Ventes et Commandes, contiennent leurs propres colonnes de dates, comme les colonnes DateExpédition et DateCommande des tables Ventes et Commandes. Vous êtes chargé de créer une table contenant le total des ventes et des commandes par année et par mois. Comment allez-vous vous y prendre pour créer un visuel avec plusieurs tables, chacune faisant référence à ses propres colonnes de dates ?
Pour résoudre ce problème, vous pouvez créer une table de dates commune qui pourra être utilisée par plusieurs tables. La section suivante explique comment faire cela dans Power BI.
Créer une table de dates commune
Voici les moyens dont vous disposez pour créer une table de dates commune :
Données sources
DAX
Power Query
Données sources
Parfois, les bases de données sources et les entrepôts de données ont déjà leurs propres tables de dates. Si l’administrateur qui a conçu la base de données a fait un travail consciencieux, ces tables peuvent être utilisées pour effectuer les tâches suivantes :
Identifier les jours de fermeture de la société
Séparer l’année civile et l’année fiscale
Identifier les week-ends et les jours de la semaine
Les tables de données sources sont abouties et prêtes à être utilisées immédiatement. Si vous disposez d'une telle table, intégrez-la à votre modèle sémantique et n'utilisez pas les autres méthodes décrites dans cette section. Nous vous recommandons d’utiliser une table de dates source, car elle est probablement partagée avec d’autres outils que vous utilisez peut-être en plus de Power BI.
Si vous ne disposez pas d’une table de données sources, vous pouvez employer d’autres méthodes de création d’une table de dates commune.
DAX
Vous pouvez utiliser les fonctions DAX (Data Analysis Expression) CALENDARAUTO() ou CALENDAR() pour générer votre table de dates commune. La fonction CALENDAR() retourne une plage de dates contiguë basée sur des dates de début et de fin entrées sous forme d’arguments dans la fonction. La fonction CALENDARAUTO() peut aussi retourner une plage contiguë et complète de dates qui sont automatiquement déterminées à partir de votre modèle sémantique. La date de début choisie correspond à la date la plus ancienne dans le modèle sémantique, et la date de fin est la date la plus récente dans le modèle sémantique. Par ailleurs, vous pouvez choisir d'inclure les données qui ont été remplies au titre du mois fiscal comme argument de la fonction CALENDARAUTO(). Dans cet exemple, la fonction CALENDAR() a été utilisée, car le but était d’afficher uniquement les données sur 10 ans à compter du 31 mai 2011 (premier jour où le service commercial a lancé le suivi de ces données).
Dans Power BI Desktop, sélectionnez Nouvelle table, puis entrez la formule DAX suivante :
Dates = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))
Vous disposez maintenant d’une colonne de dates exploitable. Cependant, cette colonne est quelque peu éparse. Par ailleurs, vous voulez afficher les colonnes pour l’année uniquement, le numéro du mois, la semaine de l’année et le jour de la semaine. Pour ce faire, sélectionnez Nouvelle colonne sur le ruban, puis entrez l’équation DAX suivante, qui récupère l’année dans votre table Date.
Year = YEAR(Dates[Date])
Vous pouvez répéter ce processus pour récupérer le numéro du mois, le numéro de la semaine et le jour de la semaine :
MonthNum = MONTH(Dates[Date])
WeekNum = WEEKNUM(Dates[Date])
DayoftheWeek = FORMAT(Dates[Date], "DDDD")
Quand vous avez terminé, votre table contient les colonnes qui figurent dans l’illustration suivante.
Vous avez maintenant créé une table de dates commune avec DAX. Ce processus ne fait qu'ajouter votre nouvelle table au modèle sémantique ; il vous reste encore à établir des relations entre votre table de dates et les tables Ventes et Commandes, puis à marquer votre table comme étant la table de dates officielle de votre modèle de données. Toutefois, avant d’effectuer ces opérations, envisagez une autre méthode de création de table de données commune : Power Query.
Power Query
Vous pouvez utiliser le langage de développement M, qui permet de créer des requêtes dans Power Query en vue de définir une table de données commune.
Sélectionnez Transformer les données dans Power BI Desktop pour être dirigé vers Power Query. Dans l’espace vide du volet Requêtes de gauche, cliquez avec le bouton droit pour ouvrir le menu déroulant suivant, puis sélectionnez Nouvelle requête > Requête vide.
Dans la vue Nouvelle requête qui s’affiche, entrez la formule M suivante pour créer une table de calendrier :
= List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))
Pour vos données de vente, la date de début doit ici correspondre à la date la plus ancienne dans les données : le 31 mai 2011. Par ailleurs, les dates à afficher doivent être celles des 10 prochaines années, y compris les dates futures. À mesure que les nouvelles données de ventes afflueront, vous n’aurez pas besoin de recréer cette table. Il est également possible de changer la durée. Dans ce cas, nous voulons un point de données pour chaque jour, mais il est également possible de définir des incréments par heures, minutes et secondes. L’illustration suivante montre le résultat.
À l’issue du processus, vous remarquerez que les dates se présentent sous la forme d’une liste et non d’une table. Pour corriger cette erreur, accédez à l’onglet Transformer sur le ruban, puis sélectionnez Convertir > Dans une table. Comme le nom l’indique, cette fonctionnalité convertit la liste en table. Vous pouvez aussi renommer la colonne ColDate.
Ensuite, vous souhaitez ajouter des colonnes à votre nouvelle table pour afficher les dates par année, mois, semaine et jour de façon à pouvoir établir une hiérarchie dans votre visuel. Votre première tâche consiste à changer le type de colonne. Pour cela, sélectionnez l’icône en regard du nom de la colonne puis, dans le menu déroulant qui s’affiche, sélectionnez le type Date.
Une fois que vous avez sélectionné le type Date, vous pouvez ajouter des colonnes pour l’année, les mois, les semaines et les jours. Accédez à Ajouter une colonne, sélectionnez le menu déroulant en dessous de Date, puis sélectionnez Année, comme dans l’illustration suivante.
Notez que Power BI a ajouté une colonne de toutes les années extraites de ColDate.
Suivez le même processus pour les mois, les semaines et les jours. Une fois que vous avez terminé ce processus, la table contient les colonnes présentes dans l’illustration suivante.
Vous êtes parvenu à créer une table de dates commune à l’aide de Power Query.
Les étapes précédentes montrent comment inclure la table dans le modèle sémantique. Maintenant, vous devez marquer votre table comme étant la table de dates officielle, ce qui permettra à Power BI de la reconnaître pour toutes les futures valeurs et de vérifier que la mise en forme est correcte.
Marquer comme table de dates officielle
Cette opération consiste dans un premier temps à rechercher la nouvelle table dans le volet Champs. Cliquez avec le bouton droit sur le nom de la table, puis sélectionnez Marquer comme table de dates, comme le montre l’illustration suivante.
En marquant votre table comme table de dates, Power BI effectue des validations pour vérifier que les données ne comportent pas de valeurs null, qu’elles sont uniques et qu’elles contiennent des valeurs de date sur une période. Vous pouvez aussi choisir de marquer certaines colonnes de votre table en tant que date, ce qui peut s’avérer utile quand votre table compte un grand nombre de colonnes. Cliquez avec le bouton droit sur la table, sélectionnez Marquer en tant que table de dates, puis sélectionnez Paramètres de la table de dates. La fenêtre suivante s’affiche, dans laquelle vous pouvez choisir la colonne qui doit être marquée comme Date.
Si vous sélectionnez Marquer comme table de dates, vous supprimez les hiérarchies générées automatiquement du champ Date de la table que vous avez marquée comme table de dates. Pour les autres champs de date, la hiérarchie automatique reste présente tant que vous n’établissez pas de relation entre ce champ et la table de dates ou que vous ne désactivez pas la fonctionnalité Date/heure automatique. Vous pouvez ajouter manuellement une hiérarchie à votre table de dates commune en cliquant avec le bouton droit sur les colonnes année, mois, semaine ou jour dans le volet Champs, puis en sélectionnant Nouvelle hiérarchie. Cette procédure est abordée plus loin dans ce module.
Créer votre visuel
Pour créer votre visuel entre les tables Ventes et Commandes, vous devez établir une relation entre cette nouvelle table de dates commune et les tables Ventes et Commandes. Vous pourrez ainsi créer des visuels en utilisant la nouvelle table de dates. Pour ce faire, accédez à l’onglet Modèle de modèle >Gérer les relations, où vous pouvez créer des relations entre la table de dates commune et les tables Commandes et Ventes en utilisant la colonne DateCommande. La capture d’écran suivante montre un exemple de relation de ce type.
Une fois que vous avez créé les relations, vous pouvez générer votre visuel Total des ventes et quantité commandée par période avec votre table de dates commune que vous avez élaborées à l’aide de la méthode DAX ou Power Query.
Pour déterminer le total des ventes, vous devez ajouter toutes les ventes, car la colonne Quantité de la table Ventes considère uniquement la recette de chaque vente, et non le chiffre d’affaires total. Pour ce faire, utilisez le calcul de mesure suivant, qui sera expliqué ultérieurement. Le calcul que vous allez utiliser au moment de créer cette mesure se présente comme suit :
#Total Sales = SUM(Sales[‘Amount’])
Quand vous avez terminé, vous pouvez créer une table en retournant sous l’onglet Visualisations et en sélectionnant le visuel Table. Comme vous voulez afficher le total des commandes et des ventes par année et mois, incluez uniquement les colonnes Année et Mois de votre table de dates, la colonne QtéCommandée et la mesure #TotalVentes. Dans la section consacrée aux hiérarchies, vous verrez qu’il est également possible d’établir une hiérarchie pour une exploration au niveau du détail, de l’année au mois. Dans cet exemple, vous pouvez les examiner côte à côte. Vous disposez maintenant d’un visuel avec une table de dates commune.
