Gestion des dates avec PowerBI

Vous le savez, dans un rapport, la gestion des dates avec PowerBI est importante.

Il est fort probable que vous ayez besoin de filtrer vos données par an ou mois. Mais également par trimestre ou par rapport à l'exercice financier quand il est différent de l’année civile.

Vous aurez peut-être des calculs à réaliser par rapport au nombre de jours, ouvrés ou non. Qu’en est-il des jours fériés ?

Vous l’avez compris, la gestion des dates dans votre rapport PowerBI a une grande importance.

Je vous propose de retrouver ici la manière dont je m’y prends.

Je commence par aller chercher les jours fériés dans une table que je nomme Fériés. Pour cela, on passe dans l’éditeur PowerQuery pour aller se connecter sur l’API des jours fériés disponible sur Data.gouv.fr !

Je vous invite à créer une nouvelle requête vide pour y mettre ce code dans l’éditeur avancé.

let
    Source = Json.Document(Web.Contents("https://calendrier.api.gouv.fr/jours-feries/" & "metropole.json")),
    #"Converti en table" = Table.FromRecords({Source}),
    #"Tableau croisé dynamique des colonnes supprimé" = Table.UnpivotOtherColumns(#"Converti en table", {}, "Attribut", "Valeur"),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Tableau croisé dynamique des colonnes supprimé",{"Valeur"}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"Attribut", type date}}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié1", "Férié ", each 1),
    #"Type modifié2" = Table.TransformColumnTypes(#"Personnalisée ajoutée",{{"Férié ", type logical}})
in
    #"Type modifié2"

Ce code va récupérer un JSON avec les données de jours fériés de la métropole puis va le transformer en table avant de la dépivoter pour obtenir une liste de jours fériés. J’ajoute une colonne de type booléenne pour y mettre la valeur “TRUE”, cela servira tout à l’heure.

Maintenant allons créer une table nommée CALENDRIER. Pour cela, dans l’éditeur de rapport, nous allons créer une Nouvelle table en passant par le menu Modélisation.

Menu PowerBI

Pour initialiser votre table, il va falloir écrire une ligne de DAX :

CALENDRIER = CALENDARAUTO() //Cette commande va générer une table automatique.
CALENDRIER = CALENDAR(MIN('factTable'[Date]),MAX('factTable'[Date])) //Cette commande va initier la table de calendrier depuis la plus petite date de votre table de fait référencée jusque la plus grande date de cette même table.

Vous avez, désormais, une bien belle table ! Il va falloir, maintenant, y ajouter des colonnes avec les informations complémentaires telles que l’année, le mois, etc.

Mais avant cela, revenons à nos jours fériés. Pour savoir si un jour de la table CALENDRIER est férié, il faut faire une liaison à notre table Fériés et ajouter une colonne dans la table CALENDRIER.

Passer dans la vue Modèle de l'éditeur de rapport pour créer la liaison entre les tables.

Vue modèle

Revenez dans la vue Donnée ou Visuel pour ajouter une colonne à la table CALENDRIER dans laquelle vous mettrez le code ci-dessous :

Jour Férié = if(RELATED('Fériés'[Férié ])=True,1,0)

Nous savons désormais si un jour est férié, il l’est quand la valeur de Jour férié est à 1.

Déterminons, maintenant, si un jour est ouvré. Commencer par récupérer le numéro de jour dans la semaine :

Jour de la semaine = WEEKDAY(CALENDRIER[Date],2) //le paramètre 2 fait démarrer la semaine le lundi

Maintenant, pour que le jour soit ouvré, alors ça doit être un jour entre lundi et vendredi inclus et surtout pas férié.

Jour ouvré = IF(CALENDRIER[Jour de la semaine]<=5,IF(CALENDRIER[Jour Férié]=0,1,0),0)

Nous pouvons ajouter ensuite pas mal d’autres colonnes, je vous mets, ci-dessous, le code Dax des colonnes que j’ai mis en place.

//Dans cet éxemple, l'année fiscale démarre le 1er Septembre

FY = IF(MONTH(CALENDRIER[Date])<9,YEAR(CALENDRIER[Date]),Year(CALENDRIER[Date])+1)

FY (Texte) = "FY"&CALENDRIER[FY]

Année = YEAR(CALENDRIER[Date])

Trimestre (Texte) = CALENDRIER[Date].[Trimestre]

Trimestre = RIGHT(CALENDRIER[Trimestre (Texte)],1)

Trimestre fiscal (Texte) = SWITCH(MONTH(CALENDRIER[Date]),
    1,"T2",
    2,"T2",
    3,"T3",
    4,"T3",
    5,"T3",
    6,"T4",
    7,"T4",
    8,"T4",
    9,"T1",
    10,"T1",
    11,"T1",
    12,"T2"
)

Trimestre fiscal = RIGHT(CALENDRIER[Trimestre fiscal (Texte)],1)

Mois (Texte) = IF(CALENDRIER[Mois]<10,"0"&CALENDRIER[Mois],CALENDRIER[Mois])&" "&CALENDRIER[Date].[Mois]

Mois = CALENDRIER[Date].[NoMois]

Semaine = WEEKNUM(CALENDRIER[Date])

Jour du mois = DAY(CALENDRIER[Date])

Jour de l'année = DATEDIFF("01/01/"&CALENDRIER[Date].[Année],CALENDRIER[Date],DAY) + 1

Semaine (Texte) = "S"&CALENDRIER[Semaine]

Mois fiscal = SWITCH(CALENDRIER[Mois],
    1,"M2 Janvier",
    2,"M3 Février",
    3,"M1 Mars",
    4,"M2 Avril",
    5,"M3 Mai",
    6,"M1 Juin",
    7,"M2 Juillet",
    8,"M3 Août",
    9,"M1 Septembre",
    10,"M2 Octobre",
    11,"M3 Novembre",
    12,"M1 décembre"
    )

Afin d’avoir une table bien rangée, je vais dans la vue Modèle pour regrouper mes colonnes dans des dossiers.

J’ai besoin de représenter une évolution dans le temps, selon une hiérarchie basée sur le calendrier fiscal qui est formée de l'exercice financier, le trimestre au sein de cet exercice financier, le mois et le jour.

Pour cela, je vais créer une nouvelle hiérarchie et y ajouter les colonnes FY (Texte), Trimestre fiscal (Texte), Mois fiscal et Jour du mois.

Voici un exemple de graphique créé avec cette hiérarchie :

La gestion des dates avec PowerBI

Cet article vous a intéressé ? N’hésiter pas à aller lire celui ou je vous partage ma bibliothèque de gabarit Visio pour PowerBI !

Gestion des dates avec PowerBI

Lire aussi :