Support Microsoft Power BI

Explore

Support Microsoft Power BI

3 | Modéliser (+ DAX)

Table de dates
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.

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 :

let


DateDebut=#date(2010, 1, 1),


DateFin=#date(2030, 12, 31),


NbJour = Duration.Days(DateFin-DateDebut),


Source = List.Dates(DateDebut, NbJour+1, #duration(1, 0, 0 ,0))

in


Source

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 :

let


    DateDebut=#date(2010, 1, 1),


    DateFin=#date(2030, 12, 31),


    NbJour = Duration.Days(DateFin-DateDebut),


    Source = List.Dates(DateDebut, NbJour+1, #duration(1, 0, 0 ,0)),


    #"Converti en table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),


    #"Type modifié" = Table.TransformColumnTypes(#"Converti en table",{{"Column1", type date}}),


    #"Colonnes renommées" = Table.RenameColumns(#"Type modifié",{{"Column1", "DateKey"}}),


    #"Semaine ISO" = Table.AddColumn(#"Colonnes renommées", "Semaine", each


    if Number.RoundDown((Date.DayOfYear([DateKey])-(Date.DayOfWeek([DateKey], Day.Monday)+1)+10)/7)=0 then


        Number.RoundDown((Date.DayOfYear(#date(Date.Year([DateKey])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([DateKey])-1,12,31), Day.Monday)+1)+10)/7)


    else if (Number.RoundDown((Date.DayOfYear([DateKey])-(Date.DayOfWeek([DateKey], Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([DateKey]),12,31), Day.Monday)+1<4)) then


    else


        Number.RoundDown((Date.DayOfYear([DateKey])-(Date.DayOfWeek([DateKey], Day.Monday)+1)+10)/7))

in


    #"Semaine ISO"

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 :


DateDebut=List.Min(tblVente[DateVente])


DateFin=List.Max(tblVente[DateVente])

Version en DAX

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

Copier/coller ce code :


Calendrier =


VAR BaseCalendar =


    CALENDAR ( DATE ( 2016, 1, 1), DATE ( 2018, 12, 31) )


return


    GENERATE(


    BaseCalendar,


    VAR BaseDate = [Date]


    VAR YearDate = YEAR (BaseDate)


    VAR MonthNumber = MONTH(BaseDate)


    VAR MonthName = FORMAT(BaseDate, "mmmm")


    VAR MonthShortName = FORMAT(BaseDate, "mmm")


    VAR YearMonthName = FORMAT(BaseDate, "mmm yyyy")


    VAR TrimNumber = TRUNC ( (MonthNumber-1) / 3 ) + 1


    VAR TrimName = "T" & TrimNumber


    VAR YearTrimName = YearDate & TrimName


    VAR NumWeek = WEEKNUM(BaseDate,21)


    RETURN ROW (


        "DateDuJour", BaseDate,


        "Année", YearDate,


        "Mois", MonthNumber,


        "Nom du mois", MonthName,


        "Nom court du mois", MonthShortName,


        "Mois Année", YearMonthName,


        "Trimestre", TrimNumber,


        "Nom trimestre", TrimName,


        "Année trimestre", YearTrimName,


        "Semaine",   NumWeek

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

⁠

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

Gallery

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.

Table de datesTable de dates

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

Table de dates
Table de dates