La table de dates : le quart d'heure qui décide de la moitié de vos mesures

Votre exercice se termine le 30 septembre. Vous écrivez une mesure de comparaison annuelle, elle renvoie un chiffre, et ce chiffre est faux. Pas d'erreur, pas d'avertissement : un nombre plausible.

Neuf fois sur dix, la cause est la même : il n'y a pas de vraie table de dates dans le modèle, ou elle n'est pas marquée comme telle.

Ce qu'une table de dates change réellement

Power BI crée automatiquement une hiérarchie de dates pour chaque colonne de type date. C'est pratique en démonstration et coûteux en production : une table cachée par colonne, aucune notion d'exercice, aucun jour ouvré, et des fonctions de décalage temporel qui ne peuvent pas s'optimiser.

Une table de dates dédiée règle quatre choses d'un coup.

Les fonctions temporelles fonctionnent. SAMEPERIODLASTYEAR, DATEADD, TOTALYTD exigent une table marquée comme table de dates, continue, sans trou. Sans cela, elles retombent sur un calcul générique : plus lent, et faux dès que l'exercice ne suit pas l'année civile.

Les axes deviennent partagés. Ventes, achats et stock se comparent sur le même calendrier plutôt que chacun sur sa propre colonne de date.

Le vocabulaire métier entre dans le modèle. Exercice, trimestre fiscal, semaine ISO, jour ouvré : autant de colonnes qui existent une fois pour toutes au lieu d'être recalculées dans chaque mesure.

Les performances suivent. Une table de dates est petite, avec quelques milliers de lignes, et le moteur sait s'en servir pour élaguer les périodes qu'il n'a pas besoin de lire.

La table que nous posons

Elle tient en une requête DAX, et elle couvre les cas que nous rencontrons.

Date =
VAR DebutExercice = 10        -- premier mois de l'exercice ; 1 pour l'année civile
VAR Debut = DATE ( 2018, 1, 1 )
VAR Fin   = DATE ( 2030, 12, 31 )
RETURN
ADDCOLUMNS (
    CALENDAR ( Debut, Fin ),
    "Année",            YEAR ( [Date] ),
    "Mois",             MONTH ( [Date] ),
    "Nom du mois",      FORMAT ( [Date], "MMMM" ),
    "Année-mois",       FORMAT ( [Date], "YYYY-MM" ),
    "Trimestre",        "T" & QUARTER ( [Date] ),
    "Jour de semaine",  WEEKDAY ( [Date], 2 ),
    "Est ouvré",        WEEKDAY ( [Date], 2 ) <= 5,
    -- L'exercice décalé : tout ce qui suit le premier mois appartient à
    -- l'exercice suivant. C'est la ligne qui manque presque toujours.
    "Exercice",
        IF (
            MONTH ( [Date] ) >= DebutExercice,
            YEAR ( [Date] ) + 1,
            YEAR ( [Date] )
        ),
    "Mois d'exercice",
        MOD ( MONTH ( [Date] ) - DebutExercice + 12, 12 ) + 1
)

Trois précisions comptent.

Les bornes couvrent tout l'historique, sans trou. Une table qui commence en 2020 alors que les écritures remontent à 2018 casse silencieusement les cumuls. Une table qui s'arrête en 2026 casse les prévisions.

Est ouvré est un booléen, pas une chaîne. Un booléen se compresse à un bit ; « Oui » / « Non » occupe bien davantage sur plusieurs milliers de lignes, et se filtre moins vite.

Les jours fériés ne sont pas dans cette version. Ils dépendent du pays et parfois de la région ; nous les chargeons depuis une table de référence plutôt que de les calculer, parce qu'une règle de calcul des fériés est fausse un jour ou l'autre.

Les trois gestes qui manquent presque toujours

Marquer la table

Dans Power BI Desktop, sélectionner la table, puis Marquer comme table de dates en désignant la colonne Date. Sans cette étape, la table existe mais les fonctions temporelles ne s'appuient pas dessus.

C'est un clic, et c'est le plus souvent oublié.

Relier sur la bonne date

Une écriture porte souvent trois dates : la date de facture, la date de comptabilisation et la date de saisie. Elles diffèrent, parfois de plusieurs semaines en fin d'exercice.

Reliez sur celle qui porte le sens métier, presque toujours la date de facture pour du chiffre d'affaires. Les deux autres restent des colonnes, utilisables ponctuellement avec USERELATIONSHIP.

Masquer la colonne technique

La colonne Date sert aux relations, pas à l'affichage. Un utilisateur qui la pose dans un visuel obtient une ligne par jour sur huit ans. Masquez-la et exposez Année, Année-mois, Exercice.

Comment vérifier que la table est juste

Trois contrôles, deux minutes.

1. Aucun trou. Comparez COUNTROWS de la table avec DATEDIFF ( MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ), DAY ) + 1. Les deux doivent être égaux.
2. L'exercice bascule au bon moment. Filtrez sur le 30 septembre et le 1er octobre : l'exercice doit changer entre les deux.
3. La comparaison annuelle tombe juste. Prenez un mois dont vous connaissez le chiffre de l'an dernier, et vérifiez que SAMEPERIODLASTYEAR le rend.

Ce troisième contrôle est le seul qui compte vraiment. Les deux premiers ne font que localiser la cause quand il échoue.

Ce qu'il faut retenir

Une table de dates prend un quart d'heure et conditionne la moitié des mesures d'un modèle. Elle doit être continue, couvrir tout l'historique, être marquée comme table de dates, être reliée sur la date qui porte le sens métier, et porter votre exercice si celui-ci ne suit pas l'année civile.

Quand une comparaison annuelle rend un chiffre faux sans erreur, c'est presque toujours là qu'il faut regarder en premier.