Comprendre les tableaux de données

L'analyse des données consiste à parcourir les données au fil du temps et à effectuer des calculs sur des périodes de temps. Par exemple, vous devrez peut-être comparer les bénéfices de l'année en cours avec les bénéfices de l'année précédente. De même, vous devrez peut-être prévoir la croissance et les bénéfices dans les années à venir. Pour cela, vous devez utiliser le regroupement et les agrégations sur une période donnée.

DAX fournit plusieurs fonctions Time Intelligence qui vous aident à effectuer la plupart de ces calculs. Cependant, ces fonctions DAX nécessitent une table de dates pour être utilisées avec les autres tables du modèle de données.

Vous pouvez soit importer une table de dates avec d'autres données à partir d'une source de données, soit créer vous-même une table de dates dans le modèle de données.

Dans ce chapitre, vous comprendrez différents aspects des tables de dates. Si vous maîtrisez les tables de dates dans le modèle de données Power Pivot, vous pouvez ignorer ce chapitre et passer aux chapitres suivants. Sinon, vous pouvez comprendre les tables de dates dans le modèle de données Power Pivot.

Qu'est-ce qu'une table de dates?

Une table de dates est une table dans un modèle de données, avec au moins une colonne de dates contiguës sur une durée requise. Il peut avoir des colonnes supplémentaires représentant différentes périodes. Cependant, ce qui est nécessaire est la colonne de dates contiguës, comme requis par les fonctions DAX Time Intelligence.

Par exemple,

  • Une table Date peut avoir des colonnes telles que Date, Mois fiscal, Trimestre fiscal et Année fiscale.

  • Une table de date peut avoir des colonnes telles que Date, Mois, Trimestre et Année.

Table de dates avec dates contiguës

Supposons que vous deviez effectuer des calculs dans la plage d'une année civile. Ensuite, la table Date doit avoir au moins une colonne avec un ensemble contigu de dates, y compris toutes les dates de cette année civile spécifique.

Par exemple, supposons que les données que vous souhaitez parcourir a les dates à partir du 1er Avril, 2014 à Novembre 30 e 2016.

  • Si vous avez à faire rapport sur une année civile, vous avez besoin d' une table de date avec une colonne - Date, qui contient toutes les dates du 1er Janvier er 2014 au 31 Décembre st 2016 dans une séquence.

  • Si vous avez à faire rapport sur un exercice, et votre fin de l'exercice est le 30 e Juin, vous avez besoin d' une table de date avec une colonne - Date, qui contient toutes les dates du 1er Juillet er 2013 au 30 Juin e 2017 dans un séquence.

  • Si vous devez générer des rapports sur les exercices civils et fiscaux, vous pouvez avoir une seule table de dates couvrant la plage de dates requise.

Votre table de dates doit contenir tous les jours de la plage de chaque année de la durée donnée. Ainsi, vous obtiendrez des dates contiguës au cours de cette période.

Si vous actualisez régulièrement vos données avec de nouvelles données, la date de fin sera prolongée d'un an ou deux, de sorte que vous n'ayez pas à mettre à jour votre table de dates souvent.

Une table de dates ressemble à la capture d'écran suivante.

Ajout d'une table de dates au modèle de données

Vous pouvez ajouter une table de dates au modèle de données de l'une des manières suivantes:

  • Importation à partir d'une base de données relationnelle ou de toute autre source de données.

  • Création d'une table de dates dans Excel, puis copie ou liaison à une nouvelle table dans Power Pivot.

  • Importation depuis Microsoft Azure Marketplace.

Création d'une table de dates dans Excel et copie dans le modèle de données

La création d'une table de dates dans Excel et la copie dans le modèle de données est le moyen le plus simple et le plus flexible de créer une table de données dans le modèle de données.

  • Ouvrez une nouvelle feuille de calcul dans Excel.

  • Type - Date dans la première ligne d'une colonne.

  • Tapez la première date de la plage de dates que vous souhaitez créer dans la deuxième ligne de la même colonne.

  • Sélectionnez la cellule, cliquez sur la poignée de recopie et faites-la glisser vers le bas pour créer une colonne de dates contiguës dans la plage de dates requise.

Par exemple, tapez 1/1/2014, cliquez sur la poignée de recopie et faites glisser vers le bas pour remplir les dates contiguës jusqu'au 31/12/2016.

  • Cliquez sur la colonne Date.
  • Cliquez sur l'onglet INSÉRER sur le ruban.
  • Cliquez sur Table.
  • Vérifiez la plage de table.
  • Cliquez sur OK.

Le tableau d'une seule colonne de dates est prêt dans Excel.

  • Sélectionnez la table.
  • Cliquez sur Copier sur le ruban.
  • Cliquez sur la fenêtre Power Pivot.
  • Cliquez sur Coller sur le ruban.

Cela ajoutera le contenu du presse-papiers à une nouvelle table dans le modèle de données. Par conséquent, vous pouvez également utiliser la même méthode pour créer une table de dates dans un modèle de données existant.

La boîte de dialogue Coller l'aperçu apparaît comme illustré dans la capture d'écran suivante.

  • Tapez Date dans la zone Nom de la table.
  • Prévisualisez les données.
  • Cochez la case - Utilisez la première ligne comme en-tête de colonne.
  • Cliquez sur OK.

Cela copie le contenu du presse-papiers dans une nouvelle table dans le modèle de données.

Maintenant, vous avez une table de dates dans le modèle de données avec une seule colonne de dates contiguës. L'en-tête de la colonne est Date comme vous l'aviez indiqué dans le tableau Excel.

Ajout de nouvelles colonnes de date à la table de dates

Ensuite, vous pouvez ajouter des colonnes calculées à la table Date selon les exigences de vos calculs.

Par exemple, vous pouvez ajouter des colonnes - Jour, Mois, Année et Trimestre comme suit -

  • Day

    =DAY('Date'[Date])

  • Month

    =MONTH('Date'[Date])

  • Year

    =YEAR('Date'[Date])

  • Trimestre

    = CONCATENER ("QTR", INT (('Date' [Mois] +2) / 3))

La table Date résultante dans le modèle de données ressemble à la capture d'écran suivante.

Ainsi, vous pouvez ajouter n'importe quel nombre de colonnes calculées à la table Date. Ce qui est important et obligatoire, c'est que la table Date doit comporter une colonne de dates contiguës couvrant la durée pendant laquelle vous effectuez des calculs.

Création d'une table de dates pour une année civile

Une année civile comprend généralement les dates du 1er janvier au 31 décembre d'une année et comprend également les jours fériés marqués pour cette année particulière. Lorsque vous effectuez des calculs, vous devrez peut-être prendre en compte uniquement les jours ouvrables, hors week-end et jours fériés.

Supposons que vous souhaitiez créer une table de dates pour l'année civile 2017.

  • Créer un tableau Excel avec une date de colonne, composée de dates contiguës du 1 er Janvier 2017-31 er Décembre 2017. ( Se reporter à la section précédente pour savoir comment faire.)

  • Copiez le tableau Excel et collez-le dans un nouveau tableau dans le modèle de données. (Reportez-vous à la section précédente pour savoir comment procéder.)

  • Nommez la table comme Calendrier.

  • Ajoutez les colonnes calculées suivantes -

    • Jour = DAY ('Calendrier' [Date])

    • Mois = MONTH ('Calendrier' [Date])

    • Année = ANNÉE ('Calendrier' [Date])

    • Jour de la semaine = FORMAT ('Calendrier' [Date], "JJJ")

    • Nom du mois = FORMAT ('Calendrier' [Date], "MMM")

Ajout de jours fériés au tableau du calendrier

Ajoutez des jours fériés au tableau du calendrier comme suit -

  • Obtenez la liste des jours fériés déclarés pour l'année.

  • Par exemple, pour les États-Unis, vous pouvez obtenir la liste des jours fériés pour toute année requise à partir du lien suivant http://www.calendar-365.com/.

  • Copiez-les et collez-les dans une feuille de calcul Excel.

  • Copiez le tableau Excel et collez-le dans un nouveau tableau dans le modèle de données.

  • Nommez la table en vacances.

  • Ensuite, vous pouvez ajouter une colonne calculée de jours fériés à la table Calendrier à l'aide de la fonction DAX LOOKUPVALUE.

=LOOKUPVALUE(Holidays[Holiday],Holidays[Date],'Calendar'[Date])

La fonction DAX LOOKUPVALUE recherche le troisième paramètre, c'est-à-dire Calendar [Date] dans le second paramètre, ie Holidays [Date] et renvoie le premier paramètre, ie Holidays [Holiday] s'il y a une correspondance. Le résultat ressemblera à ce qui est montré dans la capture d'écran suivante.

Ajout de colonnes à un exercice

Un exercice comprend généralement les dates du 1 er du mois suivant la fin de l'exercice à la fin de l'exercice suivant. Par exemple, si la fin de l'exercice est le 31 er Mars, l'exercice varie de 1 er Avril au 31 er Mars.

Vous pouvez inclure les périodes fiscales dans la table de calendrier à l'aide des formules DAX -

  • Ajouter une mesure pour FYE

    FYE:=3

  • Ajoutez les colonnes calculées suivantes -

    • Fiscal Year

      = IF ('Calendrier' [Mois] <= 'Calendrier' [FYE], 'Calendrier' [Année], 'Calendrier' [Année] +1)

    • Fiscal Month

      = IF ('Calendar' [Month] <= 'Calendar' [FYE], 12-'Calendar '[FYE] +' Calendar '[Month],' Calendar '[Month] -' Calendar '[FYE])

    • Fiscal Quarter

      = INT (('Calendar' [Fiscal Month] +2) / 3)

Définition de la propriété de la table de dates

Lorsque vous utilisez des fonctions DAX Time Intelligence telles que TOTALYTD, PREVIOUSMONTH et DATESBETWEEN, elles nécessitent des métadonnées pour fonctionner correctement. La propriété de table de dates définit ces métadonnées.

Pour définir la propriété de la table de dates -

  • Sélectionnez la table Calendrier dans la fenêtre Power Pivot.
  • Cliquez sur l'onglet Conception sur le ruban.
  • Cliquez sur Marquer comme tableau de dates dans le groupe Calendriers.
  • Cliquez sur Marquer comme tableau de dates dans la liste déroulante.

La boîte de dialogue Marquer comme tableau de dates apparaît. Sélectionnez la colonne Date dans le tableau Calendrier. Il doit s'agir de la colonne de type de données Date et doit avoir des valeurs uniques. Cliquez sur OK.