Créer une table de dates
Lors de la création d’un rapport dans Power BI, les entreprises ont souvent besoin d’effectuer des calculs basés sur la date et l’heure. Elles souhaitent connaître l’évolution de leur activité au fil des mois, trimestres, exercices, 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, mais dans certains cas, vous devrez suivre des étapes supplémentaires pour obtenir les dates au format requis par votre organisation.
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 des ventes et des commandes, contiennent leurs propres colonnes de dates, par exemple, les colonnes ShipDate et OrderDate dans les tables Sales et Orders. 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 réaliser cette tâche 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 source
DAX
Power Query
Données source
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’exercice
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 table de ce type, intégrez-la dans votre modèle sémantique et n’utilisez pas les autres méthodes indiquées dans cette section. Nous vous recommandons d’utiliser une table de dates sources, car elle est probablement partagée avec les autres outils que vous utilisez 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 de 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. Sinon, la fonction CALENDARAUTO() renvoie une plage complète et contiguë de dates qui sont automatiquement déterminées à partir de votre modèle sémantique. La date de début choisie est la date la plus ancienne qui existe dans votre modèle sémantique, et la date de fin est la date la plus récente qui existe dans votre modèle sémantique plus les données qui ont été renseignées jusqu’au mois fiscal que vous pouvez décider d’inclure comme argument dans 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 saisissez 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 saisissez 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 ajoute uniquement votre nouvelle table au modèle sémantique. Vous devez toujours établir des relations entre votre table de dates et les tables Sales et Order, puis marquer votre table comme table de dates officielle de votre modèle sémantique. Cependant, avant d’effectuer ces tâches, vous devez étudier une autre méthode de création de table de dates commune : à l’aide de Power Query.
Power Query
Vous pouvez utiliser le langage M, le langage de développement utilisé pour créer des requêtes dans Power Query, pour définir une table de dates commune.
Sélectionnez Transformer les données dans Power BI Desktop, ce qui vous dirigera 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, saisissez 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, vous souhaitez que la date de début reflète la date la plus ancienne qui se trouve dans vos 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. La figure suivante présente 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 DateCol.
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, cliquez sur le menu déroulant en dessous de Date, puis sélectionnez Year, comme dans l’illustration suivante :
Remarquerez que Power BI a ajouté une colonne de toutes les années extraites de DateCol.
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 qui figurent dans l’illustration suivante.
Vous avez réussi à utiliser Power Query pour créer une table de dates commune.
Les étapes précédentes montrent comment intégrer la table dans le modèle sémantique. Maintenant, vous devez marquer votre table comme table de dates officielle pour que Power BI puisse la reconnaître pour toutes les valeurs futures et vous assurer 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 en tant que table de dates, Power BI effectue des validations pour garantir que les données ne présentent aucune valeur nulle, sont uniques et contiennent des valeurs de dates continues 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 comme table de dates, puis sélectionnez Paramètres de la table de dates. La fenêtre suivante s’affiche, dans laquelle vous pouvez sélectionner la colonne à marquer 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 la colonne year, month, week ou day dans le volet Champs, puis en sélectionnant Nouvelle hiérarchie. Ce processus est traité plus en détail plus loin dans ce module.
Créer votre visuel
Pour créer votre visuel entre les tables Sales et Orders, vous devez établir une relation entre cette nouvelle table de dates commune et les tables Sales et Orders. Vous pourrez ainsi créer des visuels en utilisant la nouvelle table de dates. Pour ce faire, accédez à l’onglet Modèle >Gérer les relations, où vous pouvez créer des relations entre la table de dates commune et les tables Sales et Orders à l’aide de la colonne OrderDate. La capture d’écran suivante montre un exemple de relation de ce type.
Une fois les relations établies, vous pouvez créer votre visuel Ventes totales et quantité de commande par heure avec la table de dates commune que vous avez développée à 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 Amount (Montant) de la table Sales (Ventes) prend uniquement en compte le produit de chaque vente, et non le produit des ventes 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 Year et Month de votre table de dates, la colonne OrderQty et la mesure #TotalSales. 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.
