Comment créer et utiliser une table de dates dans Power BI ?

Dans ce nouvel article consacré à Power BI, je vais vous montrer différentes méthodes pour créer et utiliser une table de dates. Découvrez au travers d’exemples concrets comment obtenir simplement et rapidement une liste de dates que vous pourrez utiliser dans tous vos projets Power BI.

Table des matières

  1. Pourquoi créer une table de dates ?
  2. Créer une table calendaire en DAX avec CALENDARAUTO
  3. Créer une table calendaire en DAX avec CALENDAR
  4. Aller plus loin avec CALENDAR
  5. Créer une table de dates avec Power Query

Pourquoi créer une table de dates ?

Au cours d’un projet Power BI, il est souvent nécessaire de manipuler des dates. Cela permet d’utiliser des mesures de Time Intelligence (via des comparaisons par exemple), de proposer des filtres ou des segments (sélection d’une période personnalisée ou relative) ou bien encore pour illustrer vos données (axe de vos graphiques etc.). Vous pouvez retrouver ci-dessous des exemples de visuels qui utilisent une table de dates (calendrier). 

Exemples de représentations graphiques qui utilisent une table date

Créer une table calendaire avec CALENDARAUTO

La première méthode pour créer une table de dates peut se faire avec l’utilisation de la fonction DAX CALENDARAUTO.

Fonctionnement : Retourne une table avec une seule colonne nommée « Date » qui contient un ensemble de dates contigu. La plage de dates est calculée automatiquement en fonction des données du modèle. C’est à dire que Power Bi va parcourir votre modèle et détecter la date la plus ancienne et la plus récente pour générer une liste de dates.

Calendrier = CALENDARAUTO([fiscal_year_end_month])

// [fiscal_year_end_month] : Toute expression DAX qui retourne un entier compris entre 1 et 12. 
// En cas d’omission, la valeur par défaut est celle spécifiée dans le modèle de table de calendrier
// de l’utilisateur actuel (le cas échéant) ; sinon, la valeur par défaut est 12.

Pour créer votre calendrier, commencez par créer une nouvelle table.
Renseignez ensuite le nom de votre table et l’appel à la méthode CALENDARAUTO().

Comme on peut l’apercevoir ci-dessous, dans mon contexte Power BI retourne une erreur car il ne parvient pas à trouver dans mon modèle de données une colonne de type Date ou DateTime.  

Pour illustrer mon exemple, j’ajoute donc une nouvelle table qui contient une colonne « date » (format date) allant du 01/04/2023 au 30/04/2024. Comme on peut l’apercevoir ci-dessous, ma table « Calendrier » ne retourne plus d’erreur mais une liste de dates qui débute à partir du 01/01/2023 et qui se termine le 31/12/2024.

Il est bon de préciser que les dates provenant des colonnes personnalisées et des métriques calculées ne seront pas prises en compte.

Avantages :

  • Très simple d’utilisation et très rapide à mettre en place
  • Calendrier qui s’adapte à votre modèle de données
  • Possibilité de passer un paramètre facultatif pour s’adapter à vos besoins (ex : CALENDERAUTO(2) retournera une liste de dates comprise entre le 01/03/2023 au 02/02/2025)

Inconvénients :

  • Détection automatique. Bien que cela puisse s’avérer pratique, si votre modèle de données comporte d’autres types de dates (ex : date de naissance) votre calendrier va prendre comme date de départ une date très ancienne
Documentation officielle : https://learn.microsoft.com/fr-fr/dax/calendarauto-function-dax

Créer une table calendaire avec CALENDAR

La seconde méthode pour créer une table de date peut se faire avec l’utilisation de la fonction DAX CALENDAR.

Calendrier = CALENDAR(<start_date>, <end_date>)
//start_date : Toute expression DAX qui retourne une valeur DateHeure.
//end_date : Toute expression DAX qui retourne une valeur DateHeure.

Cette méthode attend comme paramètres une date de début et une date de fin pour fonctionner. Vous pouvez utiliser des valeurs fixes ou des valeurs dynamiques (métriques calculées) pour obtenir une table de date. L’exemple ci-dessous retournera une table de date comprise entre le 01/01/2024 et le 30/04/2024.

Je vous recommande fortement l’utilisation de variable pour ajuster cette table à votre jeu de données.

L’exemple ci-dessous vous permettra ainsi d’obtenir une table de dates qui aura comme début la date la plus ancienne de votre table A et comme date de fin aujourd’hui  :

Calendrier_2 = CALENDAR(MIN(Table_test[Date]),TODAY())

Autre exemple avec le passage de la date du début et de fin de manière statique :

Calendrier_2 = CALENDAR(DATE(2024,1,1),DATE(2024,4,30))

Avantages :

  • Simple d’utilisation et rapide à mettre en place
  • Sélection de la date de début et de fin par le concepteur du tableau de bord

Inconvénients :

  • Dans le cas de l’utilisation de paramètres statiques, si vos tables comportent des dates antérieures ou postérieures aux dates de début et de fin, votre jeu de données sera filtré
Documentation officielle : https://learn.microsoft.com/fr-fr/dax/calendar-function-dax

Aller plus loin avec CALENDAR

Maintenant que nous avons vu comment créer une table calendaire avec les méthodes CALENDARAUTO ou CALENDAR, je vais vous montrer comment obtenir des colonnes supplémentaires qui peuvent s’avérer utiles à utiliser pour vos segments (filtres) ou comme axe d’affichage pour vos graphiques. Comme vous pouvez le voir, par défaut Power Bi crée une hiérarchie de dates à partir de la colonne date. Vous pouvez ainsi obtenir l’année, le trimestre, le mois et le jour. C’est bien mais cela reste limité.

Pour diverses raisons, vous pourriez avoir besoin d’autres informations telles que le numéro de semaine, le jour de la semaine en texte, le mois en texte, l’année et le mois etc. 
Pour cela, vous pouvez utiliser les méthodes ADDCOLUMNS ainsi que FORMAT.
Voici un exemple concret que vous pouvez adapter à votre contexte :

Calendrier_etendu = 
ADDCOLUMNS (
CALENDAR(MIN(Table_test[Date]), TODAY()),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Année", YEAR ( [Date] ),
"Mois (num)", FORMAT ( [Date], "MM" ),
"Année / Mois", FORMAT ( [Date], "YYYY/MM" ),
"Mois année (txt)", FORMAT ( [Date], "mmm YYYY" ), 
"Mois (txt)", FORMAT ( [Date], "mmm" ),
"Mois (txt complet)", FORMAT ( [Date], "mmmm" ),
"Semaine", WEEKNUM ( [Date] ),
"Jours", WEEKDAY ( [Date] ),
"Jour de la semaine", FORMAT ( [Date], "dddd" ),
"Jour de de la semaine (txt)", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"Trimestre", "T" & FORMAT ( [Date], "TQ" ),
"Année / Quarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
"Mois Num - Nom complet", FORMAT ( [Date], "MM" ) & " - " & FORMAT ( [Date], "mmmm" )
)

Créer une table calendaire avec Power Query

Si vous préférez créer votre table date à partir de l’éditeur Power Query c’est possible. 
Voici un exemple de script M qui vous permettra d’obtenir une table calendaire :

let
    Source = List.Dates(Date_Debut, Nbr_jours, #duration(1, 0, 0, 0)),
    Date_Debut = #date(2024, 1, 1),
    Date_Today = DateTime.Date(DateTime.LocalNow()),
    Nbr_jours = Duration.Days(Date_Today - Date_Debut)+1,
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Colonne renommée" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Modification Type" = Table.TransformColumnTypes(#"Colonne renommée",{{"Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Modification Type", "Index", 1, 1),
    #"Num jour" = Table.AddColumn(#"Added Index", "Num Jour", each Date.Day([Date]), Int64.Type),
    #"Num semaine" = Table.AddColumn(#"Num jour", "Num Semaine", each Date.WeekOfYear([Date]), Int64.Type),
    #"Num mois" = Table.AddColumn(#"Num semaine", "Num Mois", each Date.Month([Date]), Int64.Type),
    #"Num Quarter" = Table.AddColumn(#"Num mois", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Ajout préfixe Q" = Table.TransformColumns(#"Num Quarter", {{"Quarter", each "Q" & Text.From(_, "fr-FR"), type text}}),
    #"Num Année" = Table.AddColumn(#"Ajout préfixe Q", "Année", each Date.Year([Date]), Int64.Type),
    #"Jour de la semaine (entier)" = Table.AddColumn(#"Num Année", "Jour texte", each Date.DayOfWeekName([Date]), type text),
    #"Duplication de la colonne" = Table.DuplicateColumn(#"Jour de la semaine (entier)", "Jour texte", "Jour texte (tronqué)"),
    #"Extract 3 premiers caractères JOUR" = Table.TransformColumns(#"Duplication de la colonne", {{"Jour texte (tronqué)", each Text.Start(_, 3), type text}}),
    #"Texte Mois" = Table.AddColumn(#"Extract 3 premiers caractères JOUR", "Mois texte", each Date.MonthName([Date]), type text),
    #"Duplication de la colonne mois" = Table.DuplicateColumn(#"Texte Mois", "Mois texte", "Mois texte tronqué"),
    #"Extract 3 premiers caractères MOIS" = Table.TransformColumns(#"Duplication de la colonne mois", {{"Mois texte tronqué", each Text.Start(_, 3), type text}}),
    #"Max jours / mois" = Table.AddColumn(#"Extract 3 premiers caractères MOIS", "Jours max dans le mois", each Date.DaysInMonth([Date]), Int64.Type),
    #"Jour de la semaine" = Table.AddColumn(#"Max jours / mois", "Num jour semaine", each Date.DayOfWeek([Date])+1)
in
    #"Jour de la semaine"

Maintenant que vous avez créé votre table calendaire, n’oubliez pas de cocher « marquer comme table de dates » afin de profiter de l’intelligence temporelle offerte par Power BI. Vous pouvez ensuite appliquer une relation entre votre table calendaire et vos autres tables que composent votre jeu de données.

Et vous ? Quelle méthode utilisez-vous pour faire une table calendrier dans Power BI ? Faites nous part de vos expériences en commentaire !

Il vous reste des questions ou vous souhaitez que l’on développe un point en particulier ? N’hésitez pas à nous en faire part dans les commentaires ou via notre formulaire de contact. Nous tenterons d’y répondre dans les plus brefs délais.

Cet article vous a été utile ? Faites le savoir à votre entourage professionnel.

Linkedin Free-code-camp

Quentin Moncharmont

Consultant web analyst

Consultant web analyst, je mets toute mon expérience acquise depuis 2012 au service des clients que j’accompagne. Curieux et passionné par les sujets data,  j’aime découvrir de nouveaux univers métier et partager mes connaissances. En savoir plus !

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ne rater aucune publication

Pas de spam, un email par mois pour rester en veille des derniers sujets que nous publions

Merci pour votre inscription. Vous recevrez notre prochaine newsletter très bientôt ! Aie ! Une erreur est survenue lors de votre demande - Vérifier votre saisie et recommencer. Merci

Découvrez nos autres articles web analytics

  • DataViz
  • Formations
  • Google Analytics
  • Google Tag Manager
  • Missions
  • Piano Analytics (AT)
  • Plan de marquage
  • PowerBi
  • Réalisations
12
Discutons ensemble de votre projet analytics et obtenez des solutions pertinentes à vos besoins !
Être recontacté