Editions ENI Livres | Vidéos | e-Formations
Les fonctions de date et la table du temps
Créer la table du temps
Faut-il toujours ajouter une table du temps ?
À première vue, dans un modèle de données simple (par exemple, l’import d’une feuille Excel comprenant une date), Power BI propose des outils automatiques qui semblent fonctionner correctement.
Ces outils reposent sur l’option Date/heure automatique (désactivée par défaut) que vous trouverez dans les options du fichier actif (mais aussi dans les options globales). Cette option crée automatiquement une table du temps, cachée et non modifiable, pour chaque date du modèle et génère pour le champ de type Date une hiérarchie automatique (Année, Trimestre, Mois, Jour).
Le danger ici est de voir se créer un nombre important de tables qui vont donc augmenter la taille du modèle, sans pouvoir les personnaliser (ajout de la semaine par exemple).
D’autre part, le champ Date que vous utiliserez dans votre rapport sera bien celui de la table que vous avez importée, avec ses « trous » : si aucune transaction n’a eu lieu à une date donnée, mettons le 10 janvier, cette date n’apparaît pas dans le tableau résultant, ce qui, en soit, est une perte d’information.
Les possibilités de filtrer à l’aide d’un segment seront difficiles à mettre en place. Puisque chaque date a sa propre table du temps, un segment ne peut filtrer la mesure (mettons le montant HT) que pour une date (la date de commande et pas le date de règlement - qui nécessitera un deuxième segment).
Enfin, les fonctions de Time Intelligence auront un comportement parfois curieux et pourront générer des résultats incomplets.
Il est donc recommandé de laissée désactivée l’option Date/heure automatique et de créer sa propre...
Travailler avec les semaines (WEEKNUM)
Le calcul de la semaine s’avère plus complexe qu’il n’y paraît : deux normes co-existent et celle que nous utilisons en Europe (considérée comme universelle) - la norme ISO 8601 - n’est pas celle que Power BI utilise par défaut.
C’est ce qui explique d’ailleurs que les fonctions de Time Intelligence soient peu propices aux calculs hebdomadaires.
Les normes de calcul de la semaine
Deux systèmes de calcul du numéro de semaine co-existent (dans Power BI ou ailleurs) : pour le calendrier grégorien, la semaine 1 commence le premier janvier et se termine le premier dimanche ou le premier lundi de l’année. C’est le système utilisé aux USA notamment.
Pour une semaine commençant le dimanche, la syntaxe est :
WEEKNUM([Date] ,1) Pour une semaine commençant le lundi, la syntaxe est :
WEEKNUM([Date] ,2) L’autre système, largement utilisé en Europe, « est un système de calendrier faisant partie de la norme d’horodatage ISO 8601. Le système est principalement utilisé par les gouvernements et entreprises pour baser également les années comptables et fiscales et la planification de projets à cycles hebdomadaires de travail, ainsi que pour le paiement des salaires ou des loyers (quand ceux-ci sont versés hebdomadairement) » (Wikipédia). Dans cette norme, la première semaine de l’année est celle contenant 4 jours au moins.
D’où l’existence d’un décalage entre les deux calendriers : dans le premier, une semaine 53, d’une durée comprise entre 1 et 6 jours, va s’intercaler en fin d’année (le 31 décembre 2018 fait donc partie de la semaine 201853) ; dans le second, la semaine 1 peut débuter...
Travailler avec les heures
Comme pour le travail avec les dates, le travail avec les heures demande une table à part, contenant les heures, les minutes et les secondes, si vous avez besoin de descendre à ce niveau de précision.
Il faut bien entendu que les données que vous récupérez soient elles aussi précises à l’heure, à la minute ou à la seconde près.
Pour illustrer ce cas, nous utiliserons une source indiquant des quantités produites à la minute près :
Remarquez dans cet exemple l’ajout d’une colonne identifiant (id hhnn) prévue pour faire le lien avec la table des heures et minutes. La formule est basée sur la fonction FORMAT et le format hhnn.
Il s’agit ensuite de créer une table des heures et des minutes, à l’aide d’un script DAX exactement comme pour une table de dates, avec trois particularités : GENERATESERIES qui permet de construire une série de 1 à 1440 (soit 24*60 minutes), la fonction TIME qui convertit cette série en heures : minutes, et l’identifiant pour créer la relation :
table heures minutes =
VAR _serie = GENERATESERIES(1, 1440, 1)
VAR _temps = ADDCOLUMNS( _serie, "Temps", TIME(0, [Value], 0) )
RETURN
ADDCOLUMNS(
_temps,
"id heures minutes", FORMAT([Temps], "HHNN"),
"heure complète", FORMAT([Temps], "HH:NN"),
"heure", FORMAT([Temps], "HH"),
"heure US", FORMAT([Temps], "H AM/PM"),
"minute", FORMAT([Temps], "NN"),
"AMPM", FORMAT([Temps], "AM/PM") ...Les fonctions de date
Il ne s’agit pas ici de présenter toutes les fonctions de date mais simplement d’en souligner quelques-unes qui me paraissent importantes.
DATEDIFF
La fonction DATEDIFF attend trois paramètres : la date de début, la date de fin, l’unité à compter. L’exemple suivant calcule le nombre de minutes de production dans le contexte de filtre généré par le rapport. Les unités pouvant être comptées vont de la seconde à l’année :
durée de production =
VAR debut = MIN('AUX-QTE'[heure minute])
VAR fin = MAX('AUX-QTE'[heure minute])
RETURN
DATEDIFF(debut, fin, MINUTE) Cela permet donc d’ajouter au rapport la carte ci-dessous :
La mesure est soumise au contexte de filtre (le segment est positionné sur 07h)
|
Exercice : pour mettre en pratique, vous pouvez réaliser l’exercice DATEDIFF à la fin de ce chapitre. |
NOW, TODAY
NOW() et TODAY() - sans paramètres, sont surtout utilisées avec les fonctions DATEDIFF ou DATESBETWEEN pour effectuer des calculs d’écart entre une date et maintenant (fonction NOW) ou entre une date et aujourd’hui (fonction TODAY) :
TODAY() retourne une donnée au format DATETIME mais toujours à 00h00.
DAY, MONTH, QUARTER, YEAR
Ces fonctions, très simples à utiliser, puisqu’elles reçoivent une date en seul paramètre, retournent respectivement le numéro du jour, du mois ou de l’année et s’avèrent utiles pour faire des calculs ; elles sont notamment utilisées pour générer la table du temps.
Il existe aussi les fonctions équivalentes HOUR, MINUTE et SECOND.
CALCULATE et les fonctions de Time Intelligence
Power BI offre de nombreuses fonctions de Time Intelligence (« analyse historique », « analyse temporelle ») toutes prêtes à l’emploi. Des syntax sugars comme les appellent les anglophones. Nous en verrons quelques exemples dans le chapitre suivant.
Mais encore faut-il comprendre comment elles fonctionnent et savoir revenir aux fondamentaux de ce type d’analyse en se basant sur les fonctions élémentaires.
Le cas d’un cumul hebdomadaire par exemple n’est pas prévu par les fonctions de Time Intelligence et suppose donc de créer une formule à base de CALCULATE.
Je vous conseille de lire toute cette section avant de faire les sept exercices qui y sont liés.
Les briques de base de la Time Intelligence
Toute analyse historique est basée sur la modification du contexte de filtre, autrement dit sur les données prises en compte pour le calcul. Les syntax sugars font cela implicitement, et c’est la raison pour laquelle leur utilisation peut être complexe, il est parfois difficile de savoir ce qui se passe en arrière-plan.
C’est là qu’une bonne connaissance de la fonction CALCULATE et de la manière dont le DAX filtre les données jouent un rôle capital. Associée à FILTER, à MAX et aux opérateurs > et <, CALCULATE permet de faire à peu près tout.
Dans le cadre d’un calcul cumulatif par exemple, l’une des clés est la comparaison.
Datum[Date] <= MAX(Datum[Date]) MAX([Date]) signifie ici la date courante (ce qui n’est pas nécessairement intuitif), c’est celle du contexte de filtre implicite (ou naturel) : dans une table affichant les revenus quotidiens, c’est la ligne active (mais cela fonctionnerait tout aussi bien avec les années, les...
Exercices
Créer la table du temps
Le premier objectif est simple : ajouter une table du temps au fichier chap9_or.pbix.
Cette table devra proposer les colonnes ci-dessous :
À vous de jouer : pensez bien à toutes les étapes, cette démarche est toujours la même, et finalement très rapide.
Les fonctions de date
DATEDIFF
Toujours dans le fichier chap9_or.pbix, calculez :
-
le nombre de jours écoulés entre aujourd’hui et la dernière date de cotation,
-
entre la première et la dernière date de cotation,
-
affichez à chaque fois le résultat dans une carte.
Et pour terminer, affichez dans une carte à chaque fois, le cours au premier jour de cotation et le cours au dernier jour de cotation.
CALCULATE et les fonctions de Time Intelligence
Tous les exercices de ce chapitre sont réalisés sur chap9_librairie.pbix.
Calculer la quantité l’année précédente (A-1)
Réalisez le tableau suivant où la quantité mensuelle 2019 est comparée à son équivalent 2018 :
Le tableau a été filtré pour afficher uniquement les trois premiers mois de 2019.
Calculer un cumul annuel de la quantité
Construisez la mesure permettant de réaliser le tableau suivant :
Calculer un total trimestriel glissant de la quantité
Dans le tableau ci-dessous, le total glissant est calculé en prenant en compte les trois derniers mois :
Calculer un cumul perpétuel de la quantité
Un cumul perpétuel ne s’arrête jamais, il commence sur la première ligne du tableau et se poursuit jusqu’à la dernière ligne :
À vous de construire ce tableau.
Calculer la variation par rapport au mois précédent
Calculez la différence en quantité entre le mois courant...
Corrigés
Créer la table du temps
Dans l’onglet Modélisation - groupe Calculs, cliquez sur le bouton Nouvelle table.
Saisissez le script suivant :
Datum =
VAR DebDate = DATE(2019,1,1)
VAR FinDate = DATE(2020,12,31)
RETURN
ADDCOLUMNS(CALENDAR(DebDate,FinDate),
"Annee", FORMAT([Date],"YYYY"),
"Numero du mois", MONTH([Date]),
"Annee mois", FORMAT([Date],"YYYYMM"),
"Nom court du mois", FORMAT([Date],"MMM YY"),
"Semaine", "S"&FORMAT(WEEKNUM([Date] ,2) , "0#"),
"Jour", FORMAT ( [Date], "dddd" )
) Ici, rappelez-vous, la table commence toujours un 1er janvier et se termine toujours un 31 décembre.
Pensez tout de suite à marquer cette table comme table de dates :
Dans l’onglet Outils de table - groupe Calendriers, cliquez sur le bouton Marquer comme table de dates.
Dans la fenêtre Marquer comme table de dates, ouvrez la liste déroulante Colonne de date et choisissez la colonne Date puis validez par OK.
Puis triez la colonne Nom court du mois à l’aide de la colonne Numéro du mois :
Activez la vue Données.
Dans l’onglet Outils de colonne, cliquez sur le bouton Trier par colonne et choisissez Numéro du mois.
Le même problème de tri va se poser pour Jour : si vous aviez voulu le trier correctement, il aurait fallu ajouter une colonne Numéro...
