Créer une table Master Date pour simplifier la sélection de dates MTD/QTD/YTD dans Power BI

(Last Updated On: )

Table of Contents

Préambule :

Créer une table “Master Date” dans Power BI pour effectuer des calculs de Time Intelligence sans avoir à créer des mesures spécifiques pour chaque période (MTD, QTD, YTD, etc.) permet de gagner du temps et de la simplicité dans le développement de vos rapports Power BI.

Dans un article précédent, j’expliquais comment borner les segments de sélection de dates pour ne proposer que les dates disponibles dans les données. Ici, nous allons voir comment simplifier les calculs de Time Intelligence dans un modèle de données Power BI.

Articles connexes :

Limite des méthodes traditionnelles de calculs de Time Intelligence

En analyse des données, les calculs de Time Intelligence tels que Month-To-Date (MTD), Quarter-To-Date (QTD) et Year-To-Date (YTD) sont incontournables.

Cependant, la manière traditionnelle de gérer ces calculs dans Power BI peut rapidement devenir un casse-tête. En effet, on a recours généralement à la création d’une mesure spécifique pour chaque type de calcul de Time intelligence. Imaginez avoir à créer des dizaines de mesures pour chaque type de calcul, à chaque fois que vous créez un nouveau rapport. Cela peut vite rendre le modèle encombrant et prendre du temps.

Dans cet article, je vais vous présenter une solution alternative élégante et qui peut être intégrée facilement même sur des rapports existants 🧙‍♂️

La solution

La solution proposée implique l’ajout d’une table Master Date au modèle de données Power BI. C’est une table générée en DAX qui sert de filtre pour d’autres tables, en particulier la table des dates normalement présente dans votre modèle de données. Elle est particulièrement utile pour les analyses temporelles, où vous souhaitez filtrer des données selon différentes périodes comme la semaine, le mois, le trimestre, le semestre ou l’année en cours, voire n’importe quelle période souhaitée en fait.

Comment fonctionne la Table “Master Date” ?

La table Master Date est créée en utilisant une série d’instructions DAX qui calculent des plages de dates pour différentes périodes. Ces plages de dates sont ensuite regroupées dans une seule table qui contient une colonne descriptive supplémentaire pour identifier ces périodes. Par exemple, une plage de dates pourrait être étiquettée comme “Semaine en cours” et contiendra toutes les dates de la semaine en cours.

La beauté de cette méthode réside dans sa capacité à interagir avec d’autres tables dans votre modèle grâce à des relations de données sans avoir à réécrire vos mesures pour les calculs de Time Intelligence. Une fois que la table “Master Date” est reliée à votre table de dates, vous pouvez utiliser cette dernière pour filtrer les dates dans votre rapport Power BI.

Utilisation dans un Segment de Sélection de Date

L’un des avantages majeurs de la table “Master Date” est sa facilité d’utilisation dans un segment de sélection de dates. Vous pouvez créer un segment qui utilise les colonnes descriptive de la table “Master Date” pour permettre aux utilisateurs de choisir facilement une période à analyser. Par exemple, un utilisateur peut sélectionner “Semaine en cours” dans le segment, et tous les graphiques et tableaux du tableau de bord se mettront à jour pour afficher uniquement les données de cette semaine.

Cela élimine le besoin de créer des mesures spécifiques pour chaque période, ainsi qu’une mesure de type Switch pour contrôler quelle mesure doit s’afficher dans le visuel en fonction de la sélection de l’utilisateur, ce qui peut rapidement complexifier votre modèle et le rendre plus difficile à maintenir.

Avec la table Master Date, vous utilisez les mesures existantes et appliquez simplement le filtre de la période souhaitée 😎

Comment créer et utiliser la table “Master Date”

Pour créer la table “Master Date”, commencez par aller dans le menu Modélisation de Power BI Desktop et cliquez sur Nouvelle Table.

Nous allons utiliser le code DAX suivant :

D_MasterDate = -- Table de sélection de périodes prédéfinies VAR dtmax = CALCULATE(MAX(D_Date[Date]), ALL(D_Date)) --> Date la plus récente dans les données VAR d0 = TODAY() --> Date du jour VAR dWTD = d0 - WEEKDAY(d0, 3) --> Semaine en cours VAR dLW = dWTD - 7 --> Semaine dernière VAR dMTD = DATE(YEAR(d0), MONTH(d0), 1) --> Mois en cours VAR dLM = EDATE(dMTD,-1) --> Mois dernier VAR dQTD = DATE ( YEAR ( d0 ), ROUNDUP ( DIVIDE ( MONTH ( d0 ),3 ),0 ) *3 -2, 1) --> Trimestre en cours VAR dLQ = EDATE(dQTD,-3) --> Trimestre dernier VAR dSTD = DATE ( YEAR ( d0 ), IF(MONTH(d0) < 7, 1, 7), 1) --> Semestre en cours VAR dLS = EDATE(dSTD,-6) --> Semestre dernier VAR dYTD = DATE ( YEAR ( d0 ), 1, 1 ) --> Année en cours VAR dLY = EDATE(dYTD,-12) --> Année dernière VAR dtmin = CALCULATE(MIN(D_Date[Date]), ALL(D_Date)) --> Depuis le début RETURN UNION( ADDCOLUMNS( CALENDAR(dtmin, dtmax), "IdSel", 1, "DateTexte","Tout", "DateTexte courte", "Tout", "Index", 1+DATEDIFF(dtmin,[Date],DAY)), ADDCOLUMNS( CALENDAR(dWTD, d0), "IdSel", 2, "DateTexte","Semaine en cours", "DateTexte courte", "S", "Index", 1+DATEDIFF(dWTD,[Date],DAY)), ADDCOLUMNS( CALENDAR(dLW, dWTD-1), "IdSel", 3, "DateTexte","Semaine dernière", "DateTexte courte", "S-1", "Index", 1+DATEDIFF(dLW,[Date],DAY)), ADDCOLUMNS( CALENDAR(dMTD, d0), "IdSel", 4, "DateTexte","Mois en cours", "DateTexte courte", "M", "Index", 1+DATEDIFF(dMTD,[Date],DAY)), ADDCOLUMNS( CALENDAR(dLM, dMTD-1), "IdSel", 5, "DateTexte","Mois passé", "DateTexte courte", "M-1", "Index", 1+DATEDIFF(dLM,[Date],DAY)), ADDCOLUMNS( CALENDAR(dQTD, d0), "IdSel", 6, "DateTexte","Trimestre en cours", "DateTexte courte", "T", "Index", 1+DATEDIFF(dQTD,[Date],DAY)), ADDCOLUMNS( CALENDAR(dLQ, dQTD-1), "IdSel", 7, "DateTexte","Trimestre dernier", "DateTexte courte", "T-1", "Index", 1+DATEDIFF(dLQ,[Date],DAY)), ADDCOLUMNS( CALENDAR(dYTD, d0), "IdSel", 8, "DateTexte","Année en cours", "DateTexte courte", "N", "Index", 1+DATEDIFF(dYTD,[Date],DAY)), ADDCOLUMNS( CALENDAR(dLY, dYTD-1), "IdSel", 9, "DateTexte","Année dernière", "DateTexte courte", "N-1", "Index", 1+DATEDIFF(dLY,[Date],DAY)) )

Vous pouvez bien sûr adapter les variables et les lignes ADDCOLUMNS pour ajouter des plages de dates personnalisées.

Explication du code

Pour bien comprendre comment fonctionne notre table “Master Date”, il est crucial de comprendre ce que chaque variable VAR dans le code DAX calcule. Voici le détail :

VAR dtmax: Cette variable calcule la date la plus récente présente dans votre table de données D_Date. Elle utilise la fonction MAX pour trouver cette date et la stocke pour une utilisation ultérieure.
VAR d0: Stocke la date du jour en utilisant la fonction TODAY() de DAX.
VAR dWTD: Calcule le début de la semaine en cours. Elle prend la date du jour (d0) et soustrait le numéro du jour de la semaine, en utilisant la fonction WEEKDAY. Le “3” dans WEEKDAY(d0, 3) indique que la semaine commence le lundi.
VAR dLW: Calcule le début de la semaine dernière en soustrayant 7 jours de dWTD.
VAR dMTD: Utilise les fonctions DATE, YEAR et MONTH pour créer une nouvelle date qui représente le premier jour du mois en cours.
VAR dLM: Utilise la fonction EDATE pour trouver le premier jour du mois dernier en soustrayant un mois de dMTD.
VAR dQTD: Calcule le premier jour du trimestre en cours. Il utilise plusieurs fonctions, notamment DATE, YEAR, MONTH, ROUNDUP et DIVIDE, pour effectuer ce calcul.
VAR dLQ: Semblable à dLM, mais pour le trimestre précédent. Il soustrait trois mois de dQTD.
VAR dSTD: Calcule le premier jour du semestre en cours en utilisant une logique conditionnelle pour déterminer si nous sommes dans le premier ou le second semestre de l’année.
VAR dLS: Trouve le premier jour du dernier semestre en soustrayant six mois de dSTD.
VAR dYTD: Utilise DATE et YEAR pour trouver le premier jour de l’année en cours.
VAR dLY: Utilise EDATE pour trouver le premier jour de l’année précédente en soustrayant 12 mois de dYTD.
VAR dtmin: Comme dtmax, mais pour la date la plus ancienne dans la table D_Date.

La fonction CALENDAR génère une table de dates unique qui commence et se termine aux dates spécifiées. Par exemple, CALENDAR(dtmin, dtmax) générera une table de dates allant de la date la plus ancienne (dtmin) à la date la plus récente (dtmax) dans votre table de données.

La fonction ADDCOLUMNS est utilisée pour ajouter de nouvelles colonnes à ces plages de dates. Ces colonnes supplémentaires contiennent des informations sur la période à laquelle chaque date appartient. Par exemple : Semaine en cours, Mois en cours, Trimestre en cours, Année en cours, etc.

La Fonction UNION: Enfin, toutes ces tables de dates générées avec des colonnes supplémentaires sont combinées en une seule table en utilisant la fonction UNION. Cela permet d’avoir une table “Master Date” complète qui peut être utilisée pour filtrer d’autres tables dans votre modèle Power BI.

Intégration de la table Master Date dans le modèle Power BI

Après avoir créé la table “Master Date” dans votre modèle, suivez les étapes suivantes pour pouvoir l’utiliser :

Étape 1: Créer une Nouvelle Relation

Allez dans l’onglet “Vue Modele”.
Vous verrez une représentation graphique de toutes vos tables et des relations entre elles.
Faites glisser le champ “Date” de votre table “Master Date” vers le champ “Date” correspondant à votre table des dates.

Etablissement des relations entre la table Master Date et le modèle

Étape 2: Configurer la Relation

Une fois que vous avez fait glisser et créé la relation, double-cliquez sur la flèche de la relation pour ouvrir ses propriétés.
Changez la Cardinalité de la relation à Plusieurs-à-un, le côté Plusieurs étant sur la table Master Date et le côté un sur la table des dates.
Changez la direction de filtrage croisé à Double-sens.

Pourquoi une Relation Bidirectionnelle ?

Une relation bidirectionnelle permet aux filtres de passer dans les deux sens entre les tables. Cela signifie que si vous filtrez des données dans votre table “Master Date”, cela affectera également les données dans votre table des dates, et vice versa. Cette configuration est essentielle pour le bon fonctionnement de la solution.

Étape 4: Testez Votre Modèle

Après avoir configuré la relation, testez si tout fonctionne comme prévu. Pour cela, créez quelques visualisations et insérez un segment basé sur un des champs descriptifs de la table “Master Date”, ici soit DateTexte soit DateTexte courte.

Voici le résultat 😎 :

Comparaison de la Table “Master Date” avec la méthode traditionnelle des mesures

Voici selon moi quelques avantages et inconvénients à l’utilisation d’une table “Master Date” dans un rapport Power BI.

Avantages

Flexibilité : Permet d’utiliser les mesures existantes et de les filtrer selon la période, sans avoir à créer de nouvelles mesures.
Performance : Utilise des relations entre les tables, gage de performance.
Simplicité de gestion : Réduit le nombre de mesures à gérer, simplifiant le modèle et accélérant la mise en oeuvre des calculs de Time Intelligence.

Par exemple, si vous deviez réaliser une sélection Semaine en cours, Mois en cours ou Année en cours avec des mesures traditionnelles, vous auriez à créer les mesures suivantes :

M_WTD = CALCULATE([ma mesure], DATESBETWEEN(DimDate[Date], MAX(DimDate[Date])-WEEKDAY(MAX(DimDate[Date]),3), MAX(DimDate[Date])) M_MTD = TOTALMTD([ma mesure], DimDate[Date]) M_YTD = TOTALYTD([ma mesure], DimDate[Date])

Puis une mesure SWITCH :

M_Choix = VAR choixDate = SELECTEDVALUE(ChoixDate[Valeur]) RETURN SWITCH(choixDate, "WTD", [M_WTD], "MTD", [M_MTD], "YTD", [M_YTD] )

Ainsi que créer une table ChoixDate proposant les valeurs WTD, MTD et YTD.

Enfin, il faudra insérer dans tous vos visuels la mesure [M_Choix].

Limites

Complexité Initiale : La mise en place peut être un peu plus complexe que la méthode traditionnelle de création de mesures car elle oblige à réfléchir dès le début à tous les calculs temporels que l’on souhaite faire (puis générer les plages de dates correspondantes en DAX).
Adaptabilité : Peut ne pas convenir à tous les calculs de Time Intelligence souhaités. Par exemple, on ne peut pas comparer 2 périodes avec la table Master Date, il faudra créer une mesure en DAX pour calculer l’écart N vs N-1 par exemple.
Risque : Les relations bidirectionnelles nécessitent des précautions pour éviter le risque d’ambiguïté des chemins de filtrage croisé (Plusieurs chemins de filtrage entre les tables possibles pour Power BI). Mais en vous limitant à une seule relation à double sens entre la table des dates et la table Master Date cela ne sera pas un problème.

Conclusion

La création d’une table “Master Date” dans Power BI est une méthode alternative efficace aux mesures traditionnelles de Time Intelligence, qui peut vous permettre de simplifier votre modèle de données Power BI, et peut être implémentée facilement même sur des modèles existants.

Si vous avez trouvé cet article utile et que vous souhaitez monter en compétence dans Power BI, je vous invite à franchir le pas suivant. Ma formation en ligne complète pour maîtriser Power BI est conçue pour vous emmener du niveau débutant au niveau intermédiaire, en couvrant non seulement les bases, mais aussi des techniques avancées qui feront de vous un véritable expert de l’analyse de données.

👉 Cliquez ici pour accéder à la formation complète pour maîtriser Power BI