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).
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(2024, 1, 1), 365, #duration(1, 0, 0, 0)),
#"Conversion en table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Colonnes renommées" = Table.RenameColumns(#"Conversion en table",{{"Column1", "Date"}}),
#"Type modifié" = Table.TransformColumnTypes(#"Colonnes renommées",{{"Date", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Type modifié", "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 ?
