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.

1. Ouvrez SQL Server Management Studio (ou l'éditeur de requêtes du portail Azure) sur la base, avec un compte autorisé à créer des tables.
2. Collez ce script, adaptez les bornes @DateDebut et @DateFin, puis exécutez-le (F5) :

/* ============================================================
   Table de dates Calendrier en T-SQL
   SQL Server 2012 et suivants, Azure SQL Database, Azure SQL Managed Instance.
   Résultat identique quels que soient les réglages DATEFIRST et LANGUAGE :
   aucun appel à DATENAME, à DATEPART(weekday) ni à FORMAT.
   ============================================================ */
SET NOCOUNT ON;

-- Bornes du calendrier (années complètes). Le format AAAAMMJJ est lu
-- de la même façon quelle que soit la langue de la session.
DECLARE @DateDebut date = '20200101';
DECLARE @DateFin   date = '20301231';

IF OBJECT_ID(N'dbo.Calendrier', N'U') IS NOT NULL
    DROP TABLE dbo.Calendrier;

CREATE TABLE dbo.Calendrier (
    [Date]               date         NOT NULL PRIMARY KEY,
    [Clé date]           int          NOT NULL,  -- AAAAMMJJ
    [Année]              smallint     NOT NULL,
    [Trimestre]          tinyint      NOT NULL,
    [Nom trimestre]      nvarchar(2)  NOT NULL,  -- T1
    [Année trimestre]    nvarchar(7)  NOT NULL,  -- 2025 T1
    [Mois]               tinyint      NOT NULL,
    [Nom du mois]        nvarchar(9)  NOT NULL,  -- janvier
    [Nom court du mois]  nvarchar(5)  NOT NULL,  -- janv.
    [Mois Année]         nvarchar(10) NOT NULL,  -- janv. 2025
    [Année mois]         int          NOT NULL,  -- AAAAMM, colonne de tri de Mois Année
    [Jour du mois]       tinyint      NOT NULL,
    [Jour de l'année]    smallint     NOT NULL,
    [Jour de la semaine] tinyint      NOT NULL,  -- 1 = lundi ... 7 = dimanche
    [Nom du jour]        nvarchar(8)  NOT NULL,  -- lundi
    [Nom court du jour]  nvarchar(4)  NOT NULL,  -- lun.
    [Semaine ISO]        tinyint      NOT NULL,  -- 1 à 53
    [Année ISO]          smallint     NOT NULL,  -- année de la semaine ISO
    [Année semaine ISO]  nvarchar(8)  NOT NULL,  -- 2025-S01
    [Début de mois]      date         NOT NULL,
    [Fin de mois]        date         NOT NULL,
    [Week-end]           bit          NOT NULL,
    [Jour férié]         nvarchar(20) NULL,      -- nom du jour férié, sinon NULL
    [Jour ouvré]         bit          NOT NULL   -- lundi à vendredi hors jours fériés
);

WITH Chiffres AS (
    SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS c(n)
),
Nombres AS (
    -- Jusqu'à 100 000 jours (plus de 270 ans), sans table système ni GENERATE_SERIES
    SELECT TOP (DATEDIFF(DAY, @DateDebut, @DateFin) + 1)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
    FROM Chiffres AS a
    CROSS JOIN Chiffres AS b
    CROSS JOIN Chiffres AS c
    CROSS JOIN Chiffres AS d
    CROSS JOIN Chiffres AS e
),
Jours AS (
    SELECT DATEADD(DAY, n, @DateDebut) AS [Date]
    FROM Nombres
)
INSERT INTO dbo.Calendrier
SELECT
    j.[Date],
    b.Annee * 10000 + b.Mois * 100 + b.Jour,
    b.Annee,
    t.Trimestre,
    N'T' + CAST(t.Trimestre AS nvarchar(1)),
    CAST(b.Annee AS nvarchar(4)) + N' T' + CAST(t.Trimestre AS nvarchar(1)),
    b.Mois,
    CHOOSE(b.Mois, N'janvier', N'février', N'mars', N'avril', N'mai', N'juin',
                   N'juillet', N'août', N'septembre', N'octobre', N'novembre', N'décembre'),
    m.NomCourt,
    m.NomCourt + N' ' + CAST(b.Annee AS nvarchar(4)),
    b.Annee * 100 + b.Mois,
    b.Jour,
    DATEPART(DAYOFYEAR, j.[Date]),
    s.JourSemaine,
    CHOOSE(s.JourSemaine, N'lundi', N'mardi', N'mercredi', N'jeudi',
                          N'vendredi', N'samedi', N'dimanche'),
    CHOOSE(s.JourSemaine, N'lun.', N'mar.', N'mer.', N'jeu.', N'ven.', N'sam.', N'dim.'),
    i.SemaineISO,
    i.AnneeISO,
    CAST(i.AnneeISO AS nvarchar(4)) + N'-S' + RIGHT(N'0' + CAST(i.SemaineISO AS nvarchar(2)), 2),
    DATEFROMPARTS(b.Annee, b.Mois, 1),
    EOMONTH(j.[Date]),
    CASE WHEN s.JourSemaine >= 6 THEN 1 ELSE 0 END,
    f.JourFerie,
    CASE WHEN s.JourSemaine <= 5 AND f.JourFerie IS NULL THEN 1 ELSE 0 END
FROM Jours AS j
CROSS APPLY (SELECT YEAR(j.[Date])  AS Annee,
                    MONTH(j.[Date]) AS Mois,
                    DAY(j.[Date])   AS Jour) AS b
CROSS APPLY (SELECT (b.Mois - 1) / 3 + 1 AS Trimestre) AS t
CROSS APPLY (SELECT CHOOSE(b.Mois, N'janv.', N'févr.', N'mars', N'avr.', N'mai', N'juin',
                                   N'juil.', N'août', N'sept.', N'oct.', N'nov.', N'déc.') AS NomCourt) AS m
-- Jour de la semaine sans DATEFIRST : le 1er janvier 1900 était un lundi,
-- DATEDIFF n'est pas influencé par DATEFIRST (dates postérieures à 1900).
CROSS APPLY (SELECT DATEDIFF(DAY, CAST('19000101' AS date), j.[Date]) % 7 + 1 AS JourSemaine) AS s
-- Semaine ISO (lundi, semaine 1 = celle du premier jeudi) et année ISO :
-- l'année ISO est celle du jeudi de la même semaine.
CROSS APPLY (SELECT DATEPART(ISO_WEEK, j.[Date])                   AS SemaineISO,
                    YEAR(DATEADD(DAY, 4 - s.JourSemaine, j.[Date])) AS AnneeISO) AS i
-- Date de Pâques (algorithme grégorien de Meeus, divisions entières)
CROSS APPLY (SELECT b.Annee % 19 AS a, b.Annee / 100 AS c, b.Annee % 100 AS d) AS p1
CROSS APPLY (SELECT (19 * p1.a + p1.c - p1.c / 4 - (p1.c - (p1.c + 8) / 25 + 1) / 3 + 15) % 30 AS h) AS p2
CROSS APPLY (SELECT (32 + 2 * (p1.c % 4) + 2 * (p1.d / 4) - p2.h - p1.d % 4) % 7 AS l) AS p3
CROSS APPLY (SELECT (p1.a + 11 * p2.h + 22 * p3.l) / 451 AS mm) AS p4
CROSS APPLY (SELECT DATEFROMPARTS(b.Annee,
                                  (p2.h + p3.l - 7 * p4.mm + 114) / 31,
                                  (p2.h + p3.l - 7 * p4.mm + 114) % 31 + 1) AS Paques) AS pq
-- Jours fériés en France métropolitaine (ajouter le Vendredi saint et le
-- 26 décembre pour l'Alsace et la Moselle)
CROSS APPLY (SELECT CASE
        WHEN b.Mois = 1  AND b.Jour = 1  THEN N'Jour de l''An'
        WHEN j.[Date] = DATEADD(DAY, 1, pq.Paques)  THEN N'Lundi de Pâques'
        WHEN b.Mois = 5  AND b.Jour = 1  THEN N'Fête du Travail'
        WHEN b.Mois = 5  AND b.Jour = 8  THEN N'Victoire 1945'
        WHEN j.[Date] = DATEADD(DAY, 39, pq.Paques) THEN N'Ascension'
        WHEN j.[Date] = DATEADD(DAY, 50, pq.Paques) THEN N'Lundi de Pentecôte'
        WHEN b.Mois = 7  AND b.Jour = 14 THEN N'Fête nationale'
        WHEN b.Mois = 8  AND b.Jour = 15 THEN N'Assomption'
        WHEN b.Mois = 11 AND b.Jour = 1  THEN N'Toussaint'
        WHEN b.Mois = 11 AND b.Jour = 11 THEN N'Armistice 1918'
        WHEN b.Mois = 12 AND b.Jour = 25 THEN N'Noël'
    END AS JourFerie) AS f;

-- Contrôle : une ligne par jour
SELECT COUNT(*) AS NbJours, MIN([Date]) AS Premier, MAX([Date]) AS Dernier
FROM dbo.Calendrier;

3. Dans Power BI Desktop : Accueil > Obtenir les données > SQL Server, saisissez le serveur et la base, choisissez le mode Importer puis cochez la table Calendrier.
4. Marquez la table comme table de dates sur la colonne Date, puis réglez Trier par colonne : Nom du mois et Nom court du mois par Mois, Mois Année par Année mois, Nom du jour et Nom court du jour par Jour de la semaine.

• Date et Clé date (entier AAAAMMJJ, pour les tables de faits qui portent une clé entière) ;
• Année, Trimestre (numéro, T1, 2025 T1), Mois (numéro, nom, nom court, janv. 2025 et sa colonne de tri AAAAMM) ;
• Jour du mois, Jour de l'année, Jour de la semaine (1 = lundi) avec son nom complet et court ;
• Semaine ISO, Année ISO et Année semaine ISO (2025-S01) : les premiers jours de janvier peuvent appartenir à la dernière semaine de l'année précédente ;
• Début de mois, Fin de mois, Week-end, Jour férié (nom, ou vide) et Jour ouvré (lundi à vendredi hors jours fériés).

• Le script ne dépend pas des réglages de la session : DATEPART(weekday) et DATENAME varient selon SET DATEFIRST et la langue, alors que DATEDIFF ignore DATEFIRST. Le jour de la semaine est donc calculé par l'écart avec le lundi 1er janvier 1900, et les noms français sont écrits par CHOOSE. Source (Microsoft Learn)
• DATEPART(ISO_WEEK, ...) renvoie la semaine ISO 8601 : semaine commençant le lundi, rattachée à l'année de son jeudi. Source (Microsoft Learn)
• Les jours sont générés par un produit croisé de chiffres, sans GENERATE_SERIES (réservé à SQL Server 2022 et à Azure SQL). Source (Microsoft Learn)
• CHOOSE, EOMONTH et DATEFROMPARTS existent depuis SQL Server 2012. Source (Microsoft Learn)
• Les jours fériés suivent la liste légale française, avec Pâques calculé chaque année ; ajoutez le Vendredi saint et le 26 décembre pour l'Alsace et la Moselle. Source (Service-public.fr)

1. Clic droit sur la table dans le volet Données > Marquer en tant que table de dates > Marquer en tant que table de dates, puis choisissez la colonne Date.
2. Dans la vue Modèle, reliez Calendrier[Date] à la colonne de date de chaque table de faits (un-à-plusieurs, sens unique).
3. Masquez les colonnes de date des tables de faits : les utilisateurs filtrent par le calendrier.

1. Créez une table de dates « normale » nommée Calendrier, masquez-la et laissez-la sans relation.
2. Créez une table calculée :

3. Saisissez Calendrier Vente = Calendrier, puis recommencez avec Calendrier Expédition = Calendrier.
4. Reliez Calendrier Vente[Date] à Ventes[DateVente] et Calendrier Expédition[Date] à Ventes[DateExpédition].
5. Renommez les colonnes de chaque table pour lever toute ambiguïté : Année vente, Mois vente, Année expédition, etc. Marquez chaque table comme table de dates.

• 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, T-SQL 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)