Support formation Rapports Power BI

3 | Modéliser (+ DAX)

Table de dates

La création d’une table de dates est fortement conseillée dans un environnement BI.

Une telle table contient :

une colonne avec tous les jours du 1er janvier d’une année au 31 décembre d’une autre année (colonne “DateKey”)

des colonnes qui déclinent la DateKey dans toutes les variantes souhaitées : année, mois, semaine, trimestre, etc.

Il existe de multiples façons de créer une telle table :

Une version avec Query (complexe quand on doit obtenir une semaine ISO).

Une version en DAX : facile à mettre en œuvre (copier / coller le code), gère la semaine ISO.

Une version avec Excel : permet le partage du calendrier entre plusieurs projets Power BI.

Après chargement du calendrier dans le modèle, relier la “DateClé” avec le champ Date d’une table de fait ou créer

un seul calendrier pour plusieurs dates⁠

Version avec Power Query

Nouvelles sources > Requête vide

Excel : Données > Obtenir les données > À partir d’autres sources > Requête vide

Accueil > Éditeur avancée

Effacer tout le contenu déjà présent

Copier/coller ce code :

Cliquer sur OK pour fermer l’Éditeur avancé

Onglet Transformer > Vers la table

Dans la boîte Vers la table, cliquer sur OK

Renommer la colonne Column1 en DateKey

Changer le type de la colonne en Date

Cliquer sur OK

De retour dans Query, dans la liste des requêtes (volet gauche), clic droit > Renommer la Requête 1 en Calendar ou Calendrier.

Déclinaisons de la colonne DateKey

Ajouter autant de colonnes de déclinaison de date que souhaité :

Sélectionner la colonne DateKey

Onglet Ajouter une colonne > Date. Dans le menu qui s'affiche, cliquez sur une déclinaison de date (par exemple Date > Année > Année).

Ne pas oublier de cliquer sur la colonne DateKey pour ajouter une autre déclinaison.

Enfin, fermer Query : Accueil > Fermer et appliquer.

Semaine ISO (Query)

Dans une nouvelle requête vide, copier/coller ce code :

Variante avec les valeurs d’une colonne de date d’une table

Au lieu d’utiliser une date fixe comme date de départ et de fin, on peut utiliser la valeur d’une colonne contenant des dates, comme ici avec une colonne DateVente d’une requête tblVente :

Version en DAX

Dans Power BI : onglet Modélisation > Nouvelle table.

Copier/coller ce code :

Aller plus loin :

Creating A DAX Calendar - Power BI Tips and Tricks⁠

Using GENERATE and ROW instead of ADDCOLUMNS in DAX⁠

(Vidéo ENG)

Version avec Excel

Créer un fichier Excel

Ajouter un Tableau (Insertion > Tableau)

Nommer la première colonne : DateClé (ou DateKey)

Lister toutes les dates du 1er janvier d’une année au 31 décembre d’une autre année (entre 2010 et 2050 par exemple)

Ajouter des colonnes de déclinaisons de dates.

Télécharger un exemple :

⁠

Calendrier.xlsx

120.7 kB

⁠

Un seul calendrier pour plusieurs dates

Utiliser une seule table de dates pour plusieurs colonne de dates des tables de faits suppose :

Plusieurs liens qui partent de la table de date (schéma complexe)

Avoir des noms uniques et non spécifiques : on préfèrera avoir une colonne AnnéeVente et une autre colonne AnnéeExpédition, plutôt qu’une seule colonne Année pour les 2 dates.

Dans cet exemple, nous avons une colonne DateVente et une autre DateExpédition dans une table Vente.

Créer une table de dates “normale”, avec l’une des techniques décrites ci-dessus, et nommée par exemple Calendrier. Le champ clé se nomme DateKey dans cet exemple.

Masquer la table, la déplacer dans le coin supérieur droite du schéma (il n’y aura pas de relations vers cette table, on parle de table déconnectée).

Créer une table calculée :

⁠

Dans la barre de formule, saisir Calendrier Vente = Calendrier. Une nouvelle table Calendrier Vente s’affiche dans le schéma.

Recommencer l’opération avec Calendrier Expédition = Calendrier. Une nouvelle table Calendrier Expédition s’affiche dans le schéma.

Créer une relation entre le champ DateKey de la table Calendrier Vente et le champ DateVente de la table Ventes. Idem avec la table Calendrier Expédition.

Dans chacune des 2 tables Calendrier, renommer les champs Année Vente, Mois Vente, Trimestre Vente, etc.

(option) Masquer les champs “Key” dans toutes les tables.

Version avec Power Query

Déclinaisons de la colonne DateKey

Semaine ISO (Query)

Variante avec les valeurs d’une colonne de date d’une table

Version en DAX

Version avec Excel

Un seul calendrier pour plusieurs dates

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.