• Objectifs : créer une table de dates en Power Query, en DAX ou depuis Excel ; la marquer comme table de dates ; désactiver la date/heure automatique ; gérer plusieurs colonnes de dates avec un seul calendrier.
• Prérequis : pages Schéma en étoile et Relations ; éditeur avancé de Power Query.
• Niveau : intermédiaire.

Modélisation 3 - La table de dates▶ Présenter

123456789

• Une colonne contenant tous les jours, sans trou ni doublon, du 1er janvier de la première année au 31 décembre de la dernière année.
• Des colonnes qui déclinent cette date : année, trimestre, mois (numéro et nom), semaine, jour de la semaine, etc.
• Les colonnes de noms (mois, jour) sont triées par leur numéro grâce à Trier par colonne (voir Propriétés des colonnes).

let
    DateDebut = #date(2020, 1, 1),
    DateFin = #date(2030, 12, 31),
    NbJours = Duration.Days(DateFin - DateDebut),
    Source = List.Dates(DateDebut, NbJours + 1, #duration(1, 0, 0, 0))
in
    Source

DateDebut = Date.StartOfYear(List.Min(Ventes[DateVente])),
DateFin = Date.EndOfYear(List.Max(Ventes[DateVente])),

let
    DateDebut = #date(2020, 1, 1),
    DateFin = #date(2030, 12, 31),
    NbJours = Duration.Days(DateFin - DateDebut),
    Source = List.Dates(DateDebut, NbJours + 1, #duration(1, 0, 0, 0)),
    #"Converti en table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Type modifié" = Table.TransformColumnTypes(#"Converti en table", {{"Date", type date}}),
    #"Semaine ISO" = Table.AddColumn(#"Type modifié", "Semaine", each
        if Number.RoundDown((Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7) = 0 then
            Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date]) - 1, 12, 31)) - (Date.DayOfWeek(#date(Date.Year([Date]) - 1, 12, 31), Day.Monday) + 1) + 10) / 7)
        else if (Number.RoundDown((Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7) = 53
            and (Date.DayOfWeek(#date(Date.Year([Date]), 12, 31), Day.Monday) + 1 < 4)) then
            1
        else
            Number.RoundDown((Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7),
        Int64.Type)
in
    #"Semaine ISO"

Calendrier =
VAR BaseCalendar =
    CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2030, 12, 31 ) )
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR ( BaseDate )
        VAR MonthNumber = MONTH ( BaseDate )
        VAR TrimNumber = TRUNC ( ( MonthNumber - 1 ) / 3 ) + 1
        RETURN
            ROW (
                "Année", YearDate,
                "Mois", MonthNumber,
                "Nom du mois", FORMAT ( BaseDate, "mmmm" ),
                "Nom court du mois", FORMAT ( BaseDate, "mmm" ),
                "Mois Année", FORMAT ( BaseDate, "mmm yyyy" ),
                "Trimestre", TrimNumber,
                "Nom trimestre", "T" & TrimNumber,
                "Année trimestre", YearDate & " T" & TrimNumber,
                "Semaine", WEEKNUM ( BaseDate, 21 )
            )
    )

• GENERATE combine chaque ligne de la première table avec la table renvoyée par la seconde expression.
• ROW renvoie une table d'une seule ligne, faite de paires nom et expression.
• WEEKNUM avec le paramètre 21 renvoie la semaine ISO.

1. Créez un classeur et insérez un tableau (Insertion > Tableau).
2. Nommez la première colonne Date et listez tous les jours de la période voulue.
3. Ajoutez les colonnes de déclinaison (formules ANNEE, MOIS, NO.SEMAINE.ISO...) et les colonnes métier utiles.
4. Enregistrez le classeur dans un emplacement partagé (SharePoint, OneDrive professionnel) et importez-le dans chaque projet.

• La table doit couvrir des années complètes et toutes les dates des tables de faits.
• Une colonne de type Date/heure avec des heures différentes empêche le marquage : convertissez-la en Date.
• Ne laissez pas la date/heure automatique activée avec votre propre table de dates.
• Évitez une colonne Année générique quand plusieurs calendriers coexistent : nommez chaque colonne selon son rôle.

• Une table de dates contient tous les jours, sans trou, et se relie à chaque table de faits.
• Marquez-la comme table de dates et désactivez la date/heure automatique.
• Power Query, DAX ou Excel : choisissez selon le partage et la maintenance.
• Pour plusieurs dates : relations inactives et USERELATIONSHIP, ou une table par rôle.

• Créer des tables de dates dans Power BI Desktop (conseils). Source (Microsoft Learn)
• Définir et utiliser des tables de dates dans Power BI Desktop. Source (Microsoft Learn)
• Conseils sur la date/heure automatique. Source (Microsoft Learn)
• Using GENERATE and ROW instead of ADDCOLUMNS in DAX (vidéo en anglais). Source (YouTube)