top of page
Rechercher

Table de dates Power BI : créer un calendrier en DAX ou Power Query

Groupe Kronoscope
il y a 3 jours
8 min de lecture

Une table de dates est la table la plus utilisée d'un modèle Power BI. Sans elle, les fonctions d'intelligence temporelle ne fonctionnent pas.


Trois méthodes permettent de la créer : CALENDAR, CALENDARAUTO et Power Query. Cet article les compare, puis liste les pièges qui font échouer les calculs.


Partons d'une table Ventes de six lignes.

Date

Montant

15/03/2024

1 000

20/06/2024

1 500

10/11/2024

2 000

15/03/2025

1 200

20/06/2025

1 800

10/11/2025

2 500

On écrit une mesure de comparaison à l'année précédente.

CA N-1 =
CALCULATE (
    SUM ( Ventes[Montant] ),
    SAMEPERIODLASTYEAR ( Ventes[Date] )
)

Voici ce que renvoie cette mesure dans les deux situations.

Année

CA

CA N-1 sans table de dates

CA N-1 avec table de dates

2024

4 500

Erreur


2025

5 500

Erreur

4 500

Sans table de dates, la mesure échoue. La colonne Ventes[Date] contient des trous. Avec une table de dates couvrant 2024 et 2025, la mesure renvoie 4 500 pour 2025. C'est exactement le total de 2024. La ligne 2024 reste vide, car 2023 ne contient aucune vente.




Téléchargez le pack d'exercices Table de dates

Le pack contient un classeur Excel, le code M complet et les mesures DAX. Douze exercices portent sur les bornes de la table, les jours manquants, la semaine ISO et l'exercice fiscal décalé.

La validation est automatique. Chaque réponse juste affiche une coche verte. Les données sont fictives et générées pour l'exercice.




Pourquoi une table de dates est indispensable

Les fonctions d'intelligence temporelle ne savent pas travailler sur une colonne de dates trouée. SAMEPERIODLASTYEAR, TOTALYTD et DATEADD ont besoin d'un axe temporel continu.

Une table de faits ne fournit jamais cet axe. Elle ne contient que les jours où il s'est passé quelque chose. Le pack d'exercices l'illustre avec des chiffres. La période couvre 731 jours. La table Ventes ne contient que 193 dates distinctes. 538 jours sont absents. La table de dates comble ces 538 trous.

Les cinq règles à respecter

Microsoft définit cinq conditions. Une seule règle non respectée suffit à faire échouer les calculs.

Règle

Conséquence si elle n'est pas respectée

Colonne de type Date ou Date/Heure

Le marquage de la table est refusé

Valeurs uniques

Le marquage échoue et la relation devient ambiguë

Aucune valeur vide

Le marquage échoue

Aucune date manquante

Les fonctions classiques renvoient une erreur

Années entières

Les cumuls annuels sont faux en début et en fin de plage

Schéma comparant une colonne de dates invalide, avec doublon, valeur vide, jour manquant et année incomplète, à une colonne de dates valide, et rappelant les cinq règles à respecter.

Ce que fait la date automatique à votre modèle

Power BI active par défaut l'option Date/heure automatique. Elle crée une table de dates masquée pour chaque colonne de date du modèle. Dix colonnes de date produisent dix tables masquées.

Le modèle grossit et l'actualisation ralentit. Surtout, le filtrage reste cloisonné. Une table masquée ne filtre qu'une seule colonne. Elle ne propage rien vers les autres tables.

Microsoft recommande de garder cette option pour les modèles simples seulement. Désactivez-la dès que vous créez votre propre table de dates. Le réglage se trouve dans Fichier, Options et paramètres, Options, puis Chargement des données.



Créer la table de dates en DAX

Deux fonctions DAX génèrent une table d'une seule colonne nommée Date. Elles se créent depuis l'onglet Modélisation, puis Nouvelle table.

Syntaxe

CALENDAR ( <date_début>, <date_fin> )

CALENDARAUTO ( [<mois_de_clôture>] )

CALENDAR : vous fixez les bornes

CALENDAR attend une date de début et une date de fin. Les deux bornes sont incluses.

Date =
CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2025, 12, 31 ) )

Les dates figées posent un problème. Les ventes situées hors de la plage disparaissent des visuels filtrés par la table de dates. Le total affiché devient faux, sans aucun message d'erreur.

Rendez plutôt les bornes dynamiques. Les années restent entières, et la plage suit les données.

Date =
CALENDAR (
    DATE ( YEAR ( MIN ( Ventes[Date] ) ), 1, 1 ),
    DATE ( YEAR ( MAX ( Ventes[Date] ) ), 12, 31 )
)

CALENDARAUTO : Power BI déduit les bornes

CALENDARAUTO analyse toutes les colonnes de date importées du modèle. Il retient la date la plus ancienne et la plus récente. Il remonte ensuite au début de l'exercice, et descend jusqu'à sa fin.

Date =
CALENDARAUTO ()

L'argument facultatif est le mois de clôture de l'exercice. CALENDARAUTO ( 6 ) produit des exercices qui vont du 1er juillet au 30 juin. C'est la question 11 du pack d'exercices.

Date =
CALENDARAUTO ( 6 )

Deux limites méritent d'être connues.

  • CALENDARAUTO ignore les colonnes calculées et les tables calculées. Seules les colonnes importées sont analysées.

  • La fonction renvoie une erreur si le modèle ne contient aucune colonne de date importée.

Le piège le plus fréquent vient d'une colonne de date parasite. Une date de naissance, ou une date vide convertie en 1899, étire la table sur plus d'un siècle. Contrôlez toujours la date minimale obtenue avant d'aller plus loin.

Le pack livre la version complète, avec les colonnes d'année, de trimestre, de mois, de jour et d'exercice fiscal. En voici la structure.

Date =
ADDCOLUMNS (
    CALENDAR (
        DATE ( YEAR ( MIN ( Ventes[Date] ) ), 1, 1 ),
        DATE ( YEAR ( MAX ( Ventes[Date] ) ), 12, 31 )
    ),
    "Année", YEAR ( [Date] ),
    "Num trimestre", QUARTER ( [Date] ),
    "Trimestre", "T" & QUARTER ( [Date] ),
    "Num mois", MONTH ( [Date] ),
    "Mois", FORMAT ( [Date], "MMMM" ),
    "Année-Mois", FORMAT ( [Date], "YYYY-MM" ),
    "Semaine ISO", WEEKNUM ( [Date], 21 )
)

Bon à savoir

WEEKNUM ( [Date], 21 ) renvoie bien la semaine ISO 8601. La valeur 21 n'apparaît pas dans l'IntelliSense de Power BI Desktop. Elle est pourtant documentée par Microsoft et parfaitement valide.



Créer la table de dates en Power Query

Power Query génère la table au moment de l'actualisation. La table est alors importée, et non calculée. Elle se compresse mieux dans le modèle.

Le code M complet

La fonction List.Dates produit la liste continue de dates. Le reste n'est qu'un ajout de colonnes.

let
    DateDebut = #date(2024, 1, 1),
    DateFin   = #date(2025, 12, 31),
    NbJours   = Duration.Days(DateFin - DateDebut) + 1,
    Liste     = List.Dates(DateDebut, NbJours, #duration(1,0,0,0)),
    Base      = Table.FromList(Liste, Splitter.SplitByNothing(), {"Date"})
in
    Base

Le code du pack ajoute ensuite l'année, le trimestre, le mois, le jour et l'exercice fiscal. Il est prêt à coller dans l'Éditeur avancé.

La semaine ISO, le point que Power Query ne gère pas

Date.WeekOfYear ne renvoie pas la semaine ISO 8601. La norme rattache une semaine à l'année de son jeudi. Il faut donc calculer le jeudi de la semaine, puis compter les semaines depuis le début de son année.

JeudiSemaine = Date.AddDays([Date], 3 - Date.DayOfWeek([Date], Day.Monday))

Un exemple rend la règle claire. Le 31 décembre 2025 est un mercredi. Sa semaine va du lundi 29 décembre au dimanche 4 janvier. Le jeudi de cette semaine est le 1er janvier 2026.

Cette date appartient donc à la semaine 1 de 2026, et non à la semaine 53 de 2025. C'est la question 10 du pack d'exercices.



DAX ou Power Query : comment choisir

Les deux méthodes produisent le même résultat. Elles ne se valent pas sur tous les critères.

Critère

DAX

Power Query

Mise en place

Immédiate, un copier-coller

Plus longue, code M à adapter

Extension de la plage

Automatique avec CALENDARAUTO

Manuelle, sauf borne dynamique

Partage entre projets

Copier-coller du code

Flux de données partagé

Place dans le modèle

Table calculée, recalculée à chaque actualisation

Table importée, mieux compressée

Semaine ISO

WEEKNUM avec l'argument 21

À calculer soi-même

Retenez une règle simple. Prenez DAX pour un modèle unique et un besoin rapide. Prenez Power Query dès que plusieurs rapports doivent partager le même calendrier.

Un cas prime sur les deux autres. Si la source contient déjà une table de dates, utilisez-la. Microsoft recommande cette solution en premier. Elle garantit une définition unique du temps dans l'entreprise.



Marquer comme table de dates : encore obligatoire ?

La réponse a changé récemment. Elle dépend désormais du mode d'intelligence temporelle employé.

Arbre de décision opposant l'intelligence temporelle classique, où le marquage reste obligatoire, à l'intelligence temporelle par calendriers, où il ne l'est plus, sauf deux exceptions.

Intelligence temporelle classique

C'est le mode historique, et il reste le plus répandu en production. Les fonctions reçoivent une colonne de dates en argument.

CA N-1 =
CALCULATE ( [CA], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Ici le marquage reste obligatoire. Faites un clic droit sur la table, puis Marquer comme table de dates. Power BI contrôle alors l'unicité, l'absence de valeur vide et la continuité des dates.

Attention

Marquer une table comme table de dates supprime les hiérarchies de dates automatiques du modèle. Les visuels et les mesures bâtis sur ces hiérarchies cessent de fonctionner. Contrôlez vos pages avant de valider.

Intelligence temporelle par calendriers

Microsoft a introduit un second mode dans la version de septembre 2025 de Power BI Desktop. Il s'appelle intelligence temporelle par calendriers. Il est toujours en préversion à ce jour.

Un calendrier est un jeu de métadonnées posé sur une table. Chaque colonne est rattachée à une catégorie de temps : année, trimestre, mois, semaine ou date. Les fonctions reçoivent alors le nom du calendrier, et non une colonne.

CA N-1 =
CALCULATE ( [CA], SAMEPERIODLASTYEAR ( 'Grégorien' ) )

CA cumulé semaine =
TOTALWTD ( [CA], 'Grégorien' )

Trois apports concrets justifient le détour.

  • Les dates manquantes sont acceptées. La continuité n'est plus exigée.

  • Les calendriers 4-4-5, 4-5-4 et non grégoriens sont gérés nativement.

  • Les calculs à la semaine deviennent possibles, avec TOTALWTD par exemple.

Dans ce mode, le marquage n'est plus requis. Deux exceptions subsistent. Une relation portée par une clé entière de type 20251231 impose le marquage. Les filtres de dates avancés des tableaux croisés dynamiques Excel aussi.

L'activation se fait dans Fichier, Options et paramètres, Options, puis Fonctionnalités en préversion. Cochez Enhanced DAX Time Intelligence, puis redémarrez Power BI Desktop.

Une réserve s'impose avant de basculer un modèle de production. Microsoft précise que les performances de la préversion ne sont pas représentatives du produit final. Les calendriers ne s'écrivent pas encore dans le service Power BI. Ils ne fonctionnent ni avec les modèles composites, ni avec les connexions directes.



Les six pièges qui cassent une table de dates

Voici les six erreurs rencontrées le plus souvent en projet.

  1. La plage figée trop courte. Les ventes hors plage disparaissent des visuels, sans message d'erreur. Rendez les bornes dynamiques.

  2. Le mois trié par ordre alphabétique. Avril passe avant Janvier. Sélectionnez la colonne Mois, puis Trier par colonne, puis Num mois.

  3. La semaine calculée par défaut. WEEKNUM sans argument ne suit pas la norme ISO. Ajoutez l'argument 21 en DAX, ou passez par le jeudi en Power Query.

  4. CALENDARAUTO qui remonte à 1899. Une colonne de date parasite dans le modèle étire la table. Cherchez la colonne fautive, puis excluez-la de l'import.

  5. Le marquage qui casse les hiérarchies. Les hiérarchies automatiques disparaissent au marquage. Rebâtissez vos visuels sur les colonnes de la table de dates.

  6. Les colonnes de décalage ajoutées à la table. Microsoft déconseille cette technique hors cas particulier. Elle gonfle le modèle et ralentit l'actualisation.


Une table de dates propre conditionne tout le reste du modèle. Elle alimente les comparaisons, les cumuls et les moyennes mobiles. Prenez le temps de la valider avant d'écrire la première mesure.

Pour aller plus loin, la fonction CALCULATE pilote toutes les mesures d'intelligence temporelle. La fonction DISTINCTCOUNT sert à contrôler l'unicité de votre colonne de dates.

 
 
bottom of page