Le guide ultime pour créer une table de dates dans Power BI !

Introduction

La table de dates est quelque chose de primordial dans Power BI. En effet, dès que vous voulez faire des analyses temporelles (YTD, N vs N-1...), il vous faut une table de dates, pareillement quand vous avez plusieurs tables de faits.

Mais comment en créer une ? Comment ne pas passer des heures à chaque fois ? Comment faire en sorte de distribuer un calendrier fiable aux équipes ?

Plutôt que de fouiller dans la documentation de Microsoft, on vous fait gagner du temps et propose différentes méthodes pour créer la fameuse Dim_Date.

Comment créer une table de dates dans Power BI ?

Méthode 1 : utiliser des fonctions DAX sur Power BI Desktop

Dim_Date = ADDCOLUMNS(CALENDARAUTO(), 
"Année", YEAR([Date]), 
"Trimestre", "T"&  QUARTER([Date]),
"NumMois", MONTH([Date]),
"Mois", FORMAT([Date], "mmmm"),
"Semestre", IF(MONTH([Date]) > 6 , "S2", "S1"),
"JourDansSemaine", WEEKDAY([Date], 2),
"JourOuvre/NonOuvre" , IF(WEEKDAY([Date],2) > 5, "Non ouvré","Jour Ouvré"),
"Semaine_Intevalle",

VAR DateActuelle = [Date]
VAR Lundi = DateActuelle - WEEKDAY(DateActuelle,2) + 1
VAR Dimanche = Lundi + 6
RETURN
FORMAT(Lundi, "dd/mm/yyyy") & " - " & FORMAT(Dimanche, "dd/mm/yyyy")
,
"Numéro Semaine", WEEKNUM([Date],2)

)

1. Il suffit d'aller sur la vue "Affichage Table" ou la vue de modèle
2. Ensuite, cliquer sur nouvelle table
3. coller le script ci-dessus ou le rédiger à votre façon dans la barre de formule
4. Ne pas oublier de gérer le format de la colonne Date
5. Ne pas oublier le tri des mois par la colonne numéro de mois
6. Ne pas oublier de déclarer la table en tant que table de dates pour les calculs DAX de Time Intelligence
7. Ne pas oublier de relier cette table aux tables de faits dans la vue de modèle

Et voilà ! Vous pouvez faire vos analyses temporelles en toute sérénité

N'hésitez pas à enrichir le calendrier en cliquant sur ajouter une nouvelle colonne.

NB : CALENDARAUTO() est une alternative à la fonction DAX CALENDAR() combinée à MIN() et MAX() pour obtenir une plage de dates entre une date de départ et une date de fin dynamiques. CALENDARAUTO() est vraiment utile pour avoir un script directement applicable sans avoir à le modifier pour préciser les tables où on va chercher les dates de début et de fin.

Le problème avec cette méthode, c'est le nombre de clics à faire pour tout paramétrer (le tri, le format, le marquage comme table de dates, les hiérarchies...). La méthode suivante résout ce problème.

Méthode 2 : La méthode de la vue et du script TMDL

La vue TMDL dans Power BI Desktop et service Power BI vous permet de créer des scripts, de modifier et d’appliquer des modifications aux objets de modèle sémantique à l’aide d’un éditeur de code moderne et d’un langage TMDL (Tabular Model Definition Language). Il améliore l’efficacité du développement et offre une visibilité et un contrôle complets sur les métadonnées de modèle sémantique.

La vue TMDL offre une autre expérience que la modélisation sémantique à l’aide de code au lieu d’une interface utilisateur graphique telle que la vue Modèle.

La vue TMDL offre les avantages suivants :

• Amélioration de l’efficacité du développement avec un éditeur de code enrichi qui inclut la recherche et le remplacement, des raccourcis clavier, des modifications multilignes et plus.
• Augmentez la réutilisation en scriptant, en partageant et en réutilisant facilement des scripts TMDL parmi les développeurs de modèles sémantiques. Par exemple, utilisez un site SharePoint centralisé pour partager facilement des objets de modèle sémantique réutilisables tels que des tables de calendrier ou des groupes de calcul d’intelligence temporelle.
• Plus de contrôle et de transparence en affichant tous les objets et propriétés de modèle sémantique, et en autorisant les modifications apportées aux éléments non disponibles dans l’interface utilisateur Power BI, telles que IsAvailableInMDX ou DetailRowsDefinition. (Documentation Microsoft)

Il est possible de récupérer le script TMDL d'une table de dates déjà faite et de conserver le tri, le format, la déclaration comme table de dates, les hiérarchies... c'est puissant. On peut même distribuer le script aux équipes dans un fichier partagé sur SharePoint, par exemple.

N'oubliez pas de cliquer sur Appliquer, Actualiser maintenant et de faire les relations avec les tables de faits.

Vous pouvez télécharger le script ici : TMDL_Calendrier_MYPE.txt

Méthode 3 : Script Power Query

On va d'abord créer une nouvelle requête vide et insérer le script M. Calendrier-sur-Power-Query-4.txt

Ne pas oublier de préciser le nom de votre table de faits et le nom de votre colonne Date.

Et voilà ! Ne pas hésiter à ajuster votre table avec du M grâce à l'interface visuelle de Power Query.

NB : Il est fortement recommandé d'utiliser les Dataflows pour distribuer à vos équipes et ainsi industrialiser votre table de calendrier !

Méthode 4 : L'outil externe Bravo

Il s'agit d'une extension créée par nos confrères de SQLBI. Lien pour la télécharger :  https://bravo.bi/.

Télécharger et installer Bravo. Cet outil permet de voir la taille en termes de stockage de vos colonnes et tables. Il permet aussi d'exporter sous forme d'Excel vos tables, de rendre jolies vos mesures DAX et bien sûr de créer un calendrier en 1 clic.

Méthode 5 : Claude, MCP Server, connexion au fichier TMDL...

Claude peut se connecter à votre fichier TMDL et créer Dim_Date si vous avez opté pour le format de fichier Power BI PBIP.

Claude peut aussi directement se connecter à votre Power BI et créer la table de dates grâce au MCP Server et TMDL.

Cela fonctionne mais Claude ne semble pas connaître le principe en développement KISS  : "Keep it Simple, Stupid". Il faut sûrement mieux prompter...

Conclusion

Nous avons vu beaucoup de méthodes pour créer une table de calendrier dans Power BI avec notre tutoriel pas-à-pas.

C'est tellement important mais on ne trouve pas aisément de ressources directement applicables dans le web.

Choisissez votre méthode. Celle de la vue TMDL est particulièrement appréciée chez nous ;).

Enfin, Microsoft propose des règles de conception pour cette table de dates :

• Elle doit avoir une colonne dont le type de données est date (ou date/heure), appelée colonne de dates.
• La colonne de dates doit contenir des valeurs uniques.
• La colonne de dates ne doit pas contenir de valeurs vides.
• Il ne doit pas y avoir de dates manquantes dans la colonne de dates.
• La colonne de dates doit couvrir des années entières. Une année n’est pas nécessairement une année civile (janvier-décembre).
• Si vous utilisez l’intelligence temporelle classique, la table de dates doit être marquée comme table de dates. Cela n’est pas nécessaire si vous utilisez l’intelligence temporelle basée sur le calendrier recommandée, sauf dans des circonstances spécifiques.

Cet article vous a plu et vous souhaitez approfondir ces notions ? Nous formons les particuliers comme les entreprises.
[ Découvrir nos formations Power BI ]