Créer et utiliser une table de date,

calendrier Power BI Desktop

Formateurs Power BI pédagogues

Pré-requis

Power BI

Nous allons voir ensemble comment créer et afficher une table de date / calendrier sur Power BI Desktop.

Cette manipulation devrait être obligatoire sur tous vos rapports.

À quoi sert une table de date ?

  • Utiliser des mesures de Time Intelligence
  • Filtrer une ou plusieurs tables de fait sur une même période
  • Devenir l’axe de vos graphiques

Deux méthodes pour mettre en place une table calendaire :

  • Utiliser un script DAX
  • Utiliser un script en langage M (Power Query)

Table de date avec une expression DAX

  • Vue donnée
  • Onglet « Modélisation »
  • Nouvelle Table
  • Collez le script ci-dessous
				
					Table Dates =
-- ce script crée une table du temps
ADDCOLUMNS(CALENDARAUTO(),
        "Année", YEAR([Date]),
        "Mois",MONTH([Date]),
        "Trim",QUARTER([Date]),
        "Nom du mois",FORMAT ([Date],"MMM YY"),
        "Nom du jour",FORMAT([Date],"DDD d MMM YY"),
        "AnnéeMois",FORMAT([Date],"YYYYMM"))
-- penser à trier la colonne Nom du mois par la colonne mois
-- penser à trier la colonne Nom du jour par la colonne Date
-- penser à marquer la table de date comme référence
				
			

Pour information, c’est le script de table que j’utilise dans la majeure partie de mes projets Power BI.

Remarque : Vous pouvez remplacer la fonction (CALENDAR(DATE(2018;1;1);TODAY()) par la fonction CALENDARAUTO(). Cette dernière balayera votre dataset entier pour en extraire la première et dernière valeur de date.

Suivre les commentaires :

  • Trier les colonnes de nom du mois et nom du jour. Dans le cas contraire, les mois seront triés par ordre alphabétique dans l’axe d’un graphique.
  • Marquer la table de date comme référence afin de pouvoir utiliser les fonctions de Time Intelligence de façon correcte

Table de date avec une expression Power Query

  • Obtenir des données
  • Nouvelle requête vide
  • Éditeur avancé
  • Coller le script ci-dessous
				
					let
    Source = List.Dates(StartDate, Length, #duration(1, 0, 0, 0)),
    StartDate = #date(2010, 1, 1),
    TodayDate = DateTime.Date(DateTime.LocalNow()),
    Length = Duration.Days(TodayDate - StartDate)+1,
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Inserted Day" = Table.AddColumn(#"Added Index", "Day Number", each Date.Day([Date]), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Day", "Week Number", each Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Week of Year", "Month Number", each Date.Month([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Added Prefix" = Table.TransformColumns(#"Inserted Quarter", {{"Quarter", each "Q" & Text.From(_, "en-AU"), type text}}),
    #"Inserted Year" = Table.AddColumn(#"Added Prefix", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Year", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Extracted First Characters" = Table.TransformColumns(#"Inserted Day Name", {{"Day Name", each Text.Start(_, 3), type text}}),
    #"Inserted Month Name" = Table.AddColumn(#"Extracted First Characters", "Month Name", each Date.MonthName([Date]), type text),
    #"Extracted First Characters1" = Table.TransformColumns(#"Inserted Month Name", {{"Month Name", each Text.Start(_, 3), type text}}),
    #"Inserted Days in Month" = Table.AddColumn(#"Extracted First Characters1", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Days in Month", "Day of Week", each Date.DayOfWeek([Date])+1),
    #"Inserted Year1" = Table.AddColumn(#"Inserted Day of Week", "Financial Year", each Date.Year([Date]+#duration(184,0,0,0)), Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted Year1", "Financial Month", each if ([Month Number]-6) <= 0 then [Month Number]+6 else [Month Number]-6),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Financial Week", each if [Month Number] > 6 then [Week Number]-26 else [Week Number]+26),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Conditional Column1", "Month Name", "Financial Month Name"),
    #"Inserted Quarter1" = Table.AddColumn(#"Duplicated Column", "Quarter1", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Added Conditional Column2" = Table.AddColumn(#"Inserted Quarter1", "Financial Quarter", each if [Quarter1] > 2 then [Quarter1] -2 else [Quarter1] +2),
    #"Added Prefix1" = Table.TransformColumns(#"Added Conditional Column2", {{"Financial Quarter", each "Q" & Text.From(_, "en-AU"), type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Prefix1",{"Quarter1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Year and Month", each (100*[Year])+[Month Number])
in
    #"Added Custom"
				
			

Les conseils d’Alexis dans le formatage des boutons (rouleau de peinture) :

Ne pas oublier de suivre les mêmes commentaires que le script DAX (trier la colonne nom du mois par la colonne mois + marquer la table comme table de référence de date).

Laisser un commentaire

Ces articles vous intéresseront