Créer une table de dates ou table du temps

6 janvier 2024

Pourquoi ?

Avoir sa propre table du temps est un gage de qualité de vos modèles de données (ou modèles sémantiques). 2 raisons essentielles justifient une table du temps :

  • retrouver les dates où il ne s’est rien passé. Par exemple, comment lister “les jours où je n’ai rien vendu” ?
  • utiliser les fonctions intégrées du DAX de Time Intelligence comme SAMEPERDIOLASTYEAR pour n’en citer qu’une.

Comment ?

Dans bien des cas, les sources de données sur lesquelles on s’appuient pour créer des modèles ne contiennent pas de table du temps. Dans ce cas, il faut la créer avec du code.

2 solutions s’offrent à vous : soit en M (Power Query), soit en DAX (Power BI). Sincèrement, le choix se fera ici surtout en fonction de vos compétences car l’impact sur les performances sera négligeable. Nous allons développer ici une solution en DAX.

Première solution : CALENDARAUTO

Depuis le ruban Modélisation, faire Nouvelle tableet saisir :

Dates = CALENDARAUTO()

Le Dax créé alors une table avec une seule colonne nommée Dateet qui contient une suite ininterrompue de dates, en commençant par la date la plus ancienne découverte dans le modèle de données jusqu’à la plus récente.

Avantages :

  • Rapidité de mise en oeuvre
  • Facile
  • Mise à jour automatique avec la source de données

Inconvénients :

  • Pas de contrôle sur la plage de dates sélectionnée (trop grande) ce qui peut être gênant lorsque l’on créé des segments dans les rapports

Deuxième solution : CALENDAR

Afin de contrôler l’étendue de dates, on peut utiliser la fonction CALENDAR. Cette dernière accepte 2 arguments : une date de début et une date de fin. Mais attention, elle attend des dates au format DateTime. Le plus efficace pour générer une date au format DateTime est d’utiliser la fonction DATE qui renvoie un type DateTime à partir de 3 arguments : Année, Mois, Jour.

On peut remplacer la première solution par :

Dates =
	CALENDAR (
		DATE(2022, 1, 1),
		DATE(2024, 12, 31)
	)

Cette formule renverra une table de dates avec une colonne nommée “Date” qui contient la liste de tous les jours compris entre le 01/01/2022 et le 31/12/2024.

Avantages :

  • Meilleur contrôle de l’étendue de dates qu’avec CALENDARAUTO

Inconvénients :

  • Perte de la mise à jour automatique en cas de rafraichissement des sources de données

Troisième solution

Afin de restaurer l’actualisation automatique de la table de dates sans perdre les avantages vus avec la deuxième solution, on va calculer l’année des 2 paramètres de date. Selon votre modèle, il faut choisir quelle est la colonne qui va servir de référence pour ce calcul. Il est tout à fait possible de choisir une colonne pour la date de début et une autre pour la date de fin.

Comme le jour et le mois sont toujours les mêmes (1er janvier / 31 décembre), le calcul ne s’intéresse qu’à l’année. Il faut trouver l’année la plus ancienne (le minimum de notre colonne de référence) et l’année la plus récente (le maximum de notre colonne de référence).

Les fonctions MIN et MAX permettront ce calcul et la fonction YEAR retournera uniquement l’année de ces dates. Si ma colonne de référence est la date_facture de ma table Ventes, la formule devient :

Dates = 
	CALENDAR (
		DATE(YEAR(MIN(Ventes[Date_Facture])), 1, 1),
		DATE(YEAR(MAX(Ventes[Date_Facture])), 12, 31)
	)

Dans un prochain article, j’aborderai la personnalisation de la table du temps avec de nouvelles colonnes.

Catégories

Tags