Comprendre et créer les tables de dates dans Power Pivot dans Excel

Remarque : Nous faisons de notre mieux pour vous fournir le contenu d’aide le plus récent aussi rapidement que possible dans votre langue. Cette page a été traduite automatiquement et peut donc contenir des erreurs grammaticales ou des imprécisions. Notre objectif est de faire en sorte que ce contenu vous soit utile. Pouvez-vous nous indiquer en bas de page si ces informations vous ont aidé ? Voici l’article en anglais à des fins de référence aisée.

Les tables de dates dans Power Pivot sont essentielles pour la navigation et le calcul des données dans le temps. Cet article décrit en détail les tables de dates et la façon de les créer dans Power Pivot. En particulier, cet article décrit les éléments suivants:

  • Pourquoi une table de dates est importante pour la navigation et le calcul des données par date et heure.

  • Comment utiliser Power Pivot pour ajouter une table de dates au modèle de données.

  • Découvrez comment créer des colonnes de dates (par exemple, année, mois et période) dans une table de dates.

  • Créer des relations entre les tables de dates et les tables de faits.

  • Comment utiliser les heures.

Cet article est destiné aux utilisateurs qui ont une nouveauté de Power Pivot. Néanmoins, il est important de bien comprendre l'importation de données, la création de relations et la création de colonnes calculées et de mesures.

Cet article ne décrit pas la façon d'utiliser les fonctions de Time Intelligence Dax dans les formules de mesure. Pour plus d'informations sur la création de mesures avec les fonctions Time Intelligence DAX, voir Time Intelligence dans Power pivot dans Excel.

Remarque : Dans Power Pivot, les noms «Measure» et «champ calculé» sont des synonymes. Nous utilisons la mesure Name dans l'ensemble de cet article. Pour plus d'informations, reportez-vous à la section mesures dans Power Pivot.

Sommaire

Présentation des tables de dates

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

Importer à partir d'une base de données relationnelle

Créer une table de dates dans Excel

Procédure: créer une table de dates dans Excel et copier celle-ci dans le modèle de données

Ajouter de nouvelles colonnes de date à la table de dates

Fonctions de date et d'heure DAX

Exemples de formule pour une année civile

Année

Mois

Quatre

Nom du mois

Exemples de formule pour une année fiscale

Exercice fiscal

Mois d'exercice

Trimestre d'exercice

Jours fériés ou dates spéciales

Calendrier personnalisé-périodes de 13 4 semaine

Semaine

période

Période d'exercice

Période dans l'exercice

Relations

Relations multiples

Relations inactives

Tables de dates multiples

Propriété table de dates

Utilisation des heures

Rendre les dates plus exploitables

Annexe

Convertir des dates du type de données texte vers le type de données date

Ressources supplémentaires

Présentation des tables de dates

Presque toutes les analyses de données impliquent des recherches et des comparaisons de données au fil de dates et d'heures. Par exemple, il est possible que vous vouliez additionner les montants des ventes pour le trimestre fiscal passé, puis comparer ces totaux avec d'autres trimestres ou vous pouvez calculer un solde de fin de mois pour un compte. Dans chacun de ces cas, vous utilisez des dates comme moyen de regrouper et de regrouper les transactions ou soldes de vente pour une période donnée.

Rapport Power View

Tableau croisé dynamique Total des ventes par trimestre d’exercice

Une table de dates peut contenir plusieurs représentations différentes de dates et d'heures. Par exemple, une table de dates est souvent associée à des colonnes comme exercice fiscal, mois, trimestre ou période que vous pouvez sélectionner sous forme de champs dans une liste de champs lorsque vous filtrez des données dans des tableaux croisés dynamiques ou des rapports Power View.

Liste de champs Power View

Liste de champs Power View

Pour les colonnes de date (par exemple, année, mois et trimestre) pour inclure toutes les dates comprises dans leur plage respective, la table de dates doit contenir au moins une colonne contenant un ensemble de dates contigues. Autrement dit, cette colonne doit comporter une seule ligne pour chaque jour pour chaque année incluse dans la table de dates.

Par exemple, si les données que vous voulez parcourir comportent des dates entre le 1er février 2012 et le 30 novembre et que vous rapportez une année civile, vous aurez besoin d'une table de dates ayant au moins une plage de dates comprises entre le 1er janvier 2010 et le 31 décembre 2012. Chaque année de votre table de dates doit contenir tous les jours de chaque année. Si vous enverrez régulièrement vos données avec de nouvelles données, vous voudrez peut-être exécuter la date de fin d'une année ou deux, de sorte que vous n'avez pas besoin de mettre à jour votre table de dates avec le temps.

Table de dates avec un ensemble contigu de dates

Table de dates avec des dates contiguës

Si vous déclarez une année fiscale, vous pouvez créer une table de dates avec un ensemble contigu de dates pour chaque exercice. Par exemple, si votre exercice commence le 1er mars et que vous avez des données pour les exercices financiers 2010 à la date du jour (par exemple, en FY 2013), vous pouvez créer une table de dates qui commence à 3/1/2009 et comprend au moins chaque jour de chaque année d'exercice dans la dernière date. dans l'exercice 2013.

Si vous créez des rapports sur l'année civile et l'année fiscale, vous n'avez pas besoin de créer des tables de dates distinctes. Une table de dates unique peut inclure des colonnes pour une année civile, une année fiscale et même un calendrier d'une période de 13 4 semaine. Important c'est que votre table de dates contient un ensemble contigu de dates pour toutes les années incluses.

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

Vous pouvez ajouter une table de dates à votre modèle de données de plusieurs façons:

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

  • Créez une table de dates dans Excel, puis copiez ou créez un lien vers une nouvelle table dans Power Pivot.

  • Importer à partir de Microsoft Azure Marketplace.

Examinons chacun d'eux plus en détail.

Importer à partir d'une base de données relationnelle

Si vous importez une partie ou la totalité de vos données à partir d'un Data Warehouse ou d'un autre type de base de données relationnelle, il y a déjà une table de dates et les relations entre celle-ci et le reste des données que vous importez. Les dates et le format correspondront probablement aux dates figurant dans vos données de faits, et les dates de début de début et de fin. La table de dates que vous souhaitez importer peut être très grande et contenir une plage de dates au-delà de ce que vous devrez inclure dans votre modèle de données. Vous pouvez utiliser les fonctionnalités de filtre avancées de l'Assistant importation de table de Power Pivot pour sélectionner uniquement les dates et les colonnes spécifiques dont vous avez besoin. Cela peut considérablement réduire la taille de votre classeur et améliorer les performances.

Assistant importation de table

Boîte de dialogue de l’Assistant Importation de table

Dans la plupart des cas, vous n'aurez pas besoin de créer une colonne supplémentaire telle que l'année fiscale, la semaine, le nom du mois, etc., car ils existe déjà dans la table importée. Toutefois, dans certains cas, une fois la table de dates importée dans votre modèle de données, vous devrez peut-être créer des colonnes de date supplémentaires en fonction d'un rapport particulier. Heureusement, il est facile d'utiliser DAX. Pour plus d'informations sur la création plus de champs de table de dates, voir. Chaque environnement est différent. Si vous n'êtes pas certain que vos sources de données possèdent une date ou une table de calendrier associée, contactez votre administrateur de base de données.

Créer une table de dates dans Excel

Vous pouvez créer une table de dates dans Excel, puis la copier dans une nouvelle table dans le modèle de données. C'est vraiment facile à faire et vous offre une grande souplesse.

Lorsque vous créez une table de dates dans Excel, vous commencez par une seule colonne avec une plage de dates contiguë. Vous pouvez alors créer des colonnes supplémentaires (par exemple, année, trimestre, mois, exercice, période, etc.) dans la feuille de calcul Excel à l'aide de formules Excel ou, après avoir copié le tableau dans le modèle de données, vous pouvez les créer en tant que colonnes calculées. La création de colonnes de date supplémentaires dans Power Pivot est décrite dans la section ajouter de nouvelles colonnes à la table de dates , plus loin dans cet article.

Procédure: créer une table de dates dans Excel et copier celle-ci dans le modèle de données

  1. Dans Excel, dans une feuille de calcul vide, dans la cellule a1, tapez un nom d'en-tête de colonne pour identifier une plage de dates. En règle générale, ils'agira d'une date, d'une dateheure ou d'un clédate.

  2. Dans la cellule a2, tapez une date de début. Par exemple, 1/1/2010.

  3. Cliquez sur la poignée de recopie et faites-la glisser vers le bas pour un numéro de ligne incluant une date de fin. Par exemple, 12/31/2016.

    Colonne de date dans Excel

  4. Sélectionnez toutes les lignes dans la colonne Date (y compris le nom de l'en-tête dans la cellule a1).

  5. Dans le groupe styles , cliquez sur mettre sous forme de tableau, puis sélectionnez un style.

  6. Dans la boîte de dialogue mettre sous forme de tableau , cliquez sur OK.

    Colonne de date dans Power Pivot

  7. Copiez toutes les lignes, y compris l'en-tête.

  8. Dans Power Pivot, sous l'onglet Accueil , cliquez sur coller.

  9. Dans > d'aperçu de Collage , tapez un nom tel que Date ou calendrier. Laissez lacase à cocher utiliser la première ligne comme en-têtes de colonneactivée, puis cliquez sur OK.

    Aperçu de collage

    La nouvelle table de dates (nommée calendrier dans cet exemple) dans Power Pivot ressemble à ceci:

    Table de dates dans Power Pivot

    Remarque : Vous pouvez également créer une table liée en utilisant Ajouter au modèle de données. Toutefois, cela rend votre classeur plus important, car il comporte deux versions de la table de dates. une dans Excel et une dans Power Pivot..

Remarque : La Date du nom est un mot clé dans Power Pivot. Si vous nommez la table que vous créez dans Power Pivot, vous devez placer le nom de la table entre guillemets simples dans les formules DAX qui la référencent dans un argument. Tous les exemples d'images et de formules de cet article font référence à une table de dates créée dans Power Pivot nommée calendrier.

Vous avez maintenant une table de dates dans votre modèle de données. Vous pouvez ajouter des colonnes de date (par exemple, année, mois, etc.) en utilisant DAX.

Ajouter de nouvelles colonnes de date à la table de dates

Une table de dates à une seule colonne de date ayant une seule ligne pour chaque jour pour chaque année est importante pour définir toutes les dates d'une plage de dates. Il est également nécessaire de créer une relation entre la table de faits et la table de dates. Toutefois, cette colonne de date unique avec une ligne pour chaque jour n'est pas utile lors de l'analyse par date dans un rapport de tableau croisé dynamique ou un rapport Power View. Vous voulez que votre table de dates inclue des colonnes qui vous permettent de regrouper les données d'une plage ou d'un groupe de dates. Par exemple, vous souhaiterez peut-être additionner les montants des ventes par mois ou par trimestre, ou vous pouvez créer une mesure qui calcule la croissance de l'année-revue. Dans chacun de ces cas, votre table de dates a besoin de colonnes année, mois ou trimestre qui vous permettent de regrouper les données de cette période.

Si vous avez importé votre table de dates à partir d'une source de données relationnelle, il est possible que les différents types de colonnes de dates souhaités s'incluent déjà. Dans certains cas, il est possible que vous souhaitiez modifier certaines de ces colonnes ou créer des colonnes de date supplémentaires. Ceci est particulièrement vrai si vous créez votre propre table de dates dans Excel et que vous la copiez dans le modèle de données. Heureusement, la création de colonnes de date dans PowerPivot est très simple avec les fonctions de date et d'heure dans DAX.

Conseil : Si vous n'avez pas encore utilisé le langage DAX, vous pouvez commencer à vous familiariser avec le démarrage rapide: découvrir les fondamentaux de Dax en 30 minutes sur Office.com.

Fonctions de date et d'heure DAX

Si vous avez déjà utilisé les fonctions de date et d'heure dans les formules Excel, vous serez probablement familier avec les fonctions de date et d'heure. Même si ces fonctions sont similaires à leurs équivalents dans Excel, il existe des différences importantes:

  • Les fonctions de date et d'heure DAX utilisent un type de données DateTime.

  • Elles peuvent prendre les valeurs d'une colonne comme argument.

  • Elles peuvent être utilisées pour renvoyer et/ou manipuler des valeurs de date.

Ces fonctions sont souvent utilisées lors de la création de colonnes de dates personnalisées dans une table de dates; c'est pourquoi il est important de comprendre. Nous allons utiliser un certain nombre de ces fonctions pour créer des colonnes pour l'année, le trimestre, FiscalMonth, etc.

Remarque : Les fonctions de date et d'heure dans DAX ne sont pas les mêmes que les fonctions Time Intelligence. Pour en savoir plus , voir Time Intelligence dans Power pivot dans Excel 2013.

DAX inclut les fonctions de date et d'heure suivantes:

Il existe de nombreuses autres fonctions DAX que vous pouvez utiliser dans vos formules. Par exemple, la plupart des formules décrites dans cet article utilisent des fonctions mathématiques et trigonométriques , telles que Mod et tronque, des fonctions logiques , telles que si, et des fonctions de texte telles que la mise en forme pour plus d'informations sur d'autres fonctions DAX, voir la section ressources supplémentaires dans la suite de cet article.

Exemples de formule pour une année civile

Les exemples suivants décrivent des formules permettant de créer des colonnes supplémentaires dans une table de dates nommée calendrier. Une colonne, nommée date, existe déjà et contient une plage de dates contiguë entre 1/1/2010 et 12/31/2016.

Année

= ANNÉE ([date])

Dans cette formule, la fonction year renvoie l'année de la valeur de la colonne Date. Étant donné que la valeur de la colonne date est du type de données date/heure, la fonction ANNEe sait comment renvoyer l'année à partir de celle-ci.

Colonne Année

Mois

= MOIS ([date])

Dans cette formule, de la même façon que pour la fonction ANNEe, il est préférable d'utiliser la fonction mois pour renvoyer une valeur de mois à partir de la colonne Date.

Colonne Mois

Quatre

= ENT (([mois] + 2)/3)

Dans cette formule, nous utilisons la fonction ent pour renvoyer une valeur de date sous forme d'entier. L'argument que nous spécifions pour la fonction ent est la valeur de la colonne mois, ajoutez 2, puis divisez-la par 3 pour obtenir notre trimestre, 1 à 4.

Colonne Trimestre

Nom du mois

= FORMAT ([date], "MMMM")

Dans cette formule, pour obtenir le nom du mois, nous utilisons la fonction format pour convertir une valeur numérique de la colonne Date en texte. Nous spécifions la colonne Date comme premier argument, puis le format; Nous voulons que le nom du mois affiche tous les caractères et nous utilisons donc «mmmm». Notre résultat est semblable à ce qui suit:

Colonne Nom de mois

Si nous voulons renvoyer le nom de mois abrégé en trois lettres, nous devons utiliser «mmm» dans l'argument de format.

Jour de la semaine

= FORMAT ([date], "DDD")

Dans cette formule, nous utilisons la fonction FORMAT pour obtenir le nom du jour. Étant donné que nous voulons simplement un nom de jour abrégé, nous spécifions «DDD» dans l'argument de format.

Colonne Jour de la semaine
Exemple de tableau croisé dynamique

Dès lors que vous disposez de champs pour les dates (par exemple, année, trimestre, mois, etc.), vous pouvez les utiliser dans un rapport ou un tableau croisé dynamique. Par exemple, l'image ci-après illustre le champ SalesAmount de la table de faits de ventes dans valeurs et l'année et le trimestre de la table de dimension du calendrier dans lignes. SalesAmount est agrégé pour le contexte année et trimestre.

Exemple de tableau croisé dynamique

Exemples de formule pour une année fiscale

Exercice fiscal

= Si ([mois] < = 6, [année], [année] + 1)

Dans cet exemple, l'exercice commence le 1er juillet.

Il n'existe pas de fonction permettant d'extraire une année fiscale à partir d'une valeur de date, car les dates de début et de fin d'une année fiscale sont souvent différentes de celles d'une année civile. Pour obtenir l'année fiscale, nous utilisons d'abord une fonction si pour tester si la valeur du mois est inférieure ou égale à 6. Dans le deuxième argument, si la valeur pour month est inférieure ou égale à 6, renvoyez la valeur de la colonne Year. Si ce n'est pas le cas, renvoyez la valeur de Year et ajoutez 1.

Colonne Exercice

Une autre méthode pour spécifier la valeur d'un mois de fin d'exercice consiste à créer une mesure spécifiant simplement le mois. Par exemple, FYE: = 6. Vous pouvez ensuite référencer le nom de la mesure à la place du numéro de mois. Par exemple, = si ([mois] < = [FYE], [année], [année] + 1). Cela offre davantage de souplesse lors du référencement du mois de fin de l'exercice dans différentes formules.

Mois d'exercice

= Si ([mois] < = 6, 6 + [mois], [mois]-6)

Dans cette formule, spécifiez si la valeur de [Month] est inférieure ou égale à 6, puis utilisez 6 et ajoutez la valeur du mois, sinon soustraiiez 6 de la valeur de [Month].

Colonne Mois d’exercice

Trimestre d'exercice

= ENT (([FiscalMonth] + 2)/3)

La formule que nous utilisons pour FiscalQuarter est similaire à celle du trimestre de l'année calendaire. La seule différence réside dans le fait que [FiscalMonth] au lieu de [mois].

Colonne Trimestre d’exercice

Jours fériés ou dates spéciales

Il est possible que vous souhaitiez inclure une colonne de date indiquant que certaines dates sont des jours fériés ou des dates spéciales. Par exemple, il est possible que vous vouliez additionner les totaux des ventes pour les nouveaux ans en ajoutant un champ jour férié à un tableau croisé dynamique, en tant que segment ou en filtrant. Dans les autres cas, vous voudrez probablement exclure ces dates d'autres colonnes de date ou d'une mesure.

Les jours fériés ou les journées spéciales sont assez simples. Vous pouvez créer un tableau dans Excel contenant les dates que vous souhaitez inclure. Vous pouvez ensuite copier ou utiliser l'application ajouter au modèle de données pour l'ajouter au modèle de données en tant que table liée. Dans la plupart des cas, il n'est pas nécessaire de créer une relation entre la table et la table calendrier. Toutes les formules qui font référence à cette fonction peuvent utiliser la fonction ValRech pour renvoyer des valeurs.

Vous trouverez ci-dessous un exemple d'une table créée dans Excel qui inclut les jours fériés à ajouter à la table de dates:

Date

Jour férié

1/1/2010

Nouvel an

11/25/2010

Fête du travail

12/25/2010

Fêtes

1/1/2011

Nouvel an

11/24/2011

Fête du travail

12/25/2011

Fêtes

01/01/2012

Nouvel an

22.11.12

Fête du travail

12/25/2012

Fêtes

1/1/2013

Nouvel an

11/28/2013

Fête du travail

12/25/2013

Fêtes

11/27/2014

Fête du travail

12/25/2014

Fêtes

01/01/2014

Nouvel an

11/27/2014

Fête du travail

12/25/2014

Fêtes

1/1/2015

Nouvel an

11/26/2014

Fête du travail

12/25/2015

Fêtes

01.01.16

Nouvel an

11/24/2016

Fête du travail

12/25/2016

Fêtes

Dans la table de dates, nous créons une colonne nommée jour férié et utilisons une formule comme celle-ci:

= VALRECH (jours fériés [jour férié], jours fériés [date], calendrier [date])

Examinons cette formule plus attentivement.

Nous utilisons la fonction VALRECH pour obtenir les valeurs de la colonne jours fériés dans la table jours fériés. Dans le premier argument, spécifiez la colonne où se trouve la valeur de résultat. La colonne jour férié est spécifiée dans la table jours fériés , car il s'agit de la valeur que nous voulons renvoyer.

= VALRECH (jours fériés [jour férié], jours fériés [date], calendrier [date])

Nous spécifions ensuite le second argument, la colonne de recherche qui contient les dates que nous voulons rechercher. Nous spécifions la colonne Date dans la table jours fériés , comme suit:

= VALRECH (jours fériés [jour férié],jours fériés [date], calendrier [date])

Enfin, spécifiez la colonne de notre table de calendrier qui contient les dates que nous voulons rechercher dans la table jours fériés . Il s'agit de la colonne Date de la table calendrier .

= VALRECH (jours fériés [jour férié], jours fériés [date],calendrier [date])

La colonne jour férié renvoie le nom du jour férié pour chaque ligne contenant une valeur de date correspondant à une date dans la table jours fériés.

Table Jours fériés

Calendrier personnalisé-périodes de 13 4 semaine

Certaines organisations, comme les services de vente au détail ou d'alimentation, présentent souvent des périodes différentes, telles que des périodes de 13 4 semaine. Avec un calendrier d'une période de 13 4 semaine, chaque période est de 28 jours; C'est pourquoi chaque période comporte 4 lundi, 4 mardis, 4 mercredis, etc. Chaque période comporte le même nombre de jours et les jours fériés sont généralement compris dans la même période chaque année. Vous pouvez décider de commencer une période à n'importe quel jour de la semaine. Comme avec les dates d'un calendrier ou d'une année fiscale, vous pouvez utiliser DAX pour créer des colonnes supplémentaires avec des dates personnalisées.

Dans les exemples ci-dessous, la première période complète commence le premier dimanche de l'exercice. Dans ce cas, l'exercice commence le 7/1.

Semaine

Cette valeur nous indique le numéro de la semaine commençant par la première semaine complète de l'exercice. Dans cet exemple, la première semaine complète commence le dimanche, la première semaine complète de la première année de la table du calendrier commence en fait sur 7/4/2010 et continue sur la dernière semaine complète de la table calendrier. Même si cette valeur n'est pas très utile dans le cadre de l'analyse, il est nécessaire de procéder au calcul d'une utilisation dans d'autres formules de 28 jours.

= ENT ([date]-40356)/7)

Examinons cette formule plus attentivement.

Tout d'abord, nous créons une formule qui renvoie les valeurs de la colonne date sous forme d'entier, comme suit:

= ENT ([date])

Nous voulons alors chercher le premier dimanche de la première année fiscale. Nous voyons qu'il s'agit de 7/4/2010.

Colonne Semaine

Vous pouvez maintenant soustraire 40356 (qui est le nombre entier de 6/27/2010, le dernier dimanche de l'exercice précédent) de cette valeur afin d'obtenir le nombre de jours écoulés depuis le début des jours dans notre table de calendrier, comme suit:

= ENT ([date]-40356)

Divisez ensuite le résultat par 7 (jours dans une semaine), comme suit:

= ENT (([date]-40356)/7)

Le résultat est semblable à ce qui suit:

Colonne Semaine

période

La période de ce calendrier personnalisé comporte 28 jours et commence toujours un dimanche. Cette colonne renverra le numéro de la période commençant par le premier dimanche de la première année fiscale.

= ENT (([semaine] + 3)/4)

Examinons cette formule plus attentivement.

Tout d'abord, nous créons une formule qui renvoie une valeur de la colonne semaine sous forme d'entier, comme suit:

=Ent ([semaine])

Ajoutez ensuite 3 à cette valeur, comme suit:

= ENT ([semaine]+ 3)

Divisez ensuite le résultat par 4, comme suit:

= ENT (([semaine] + 3)/4)

Le résultat est semblable à ce qui suit:

Colonne Période

Période d'exercice

Cette valeur renvoie l'exercice correspondant à une période.

= ENT (([période] + 12)/13) + 2008

Examinons cette formule plus attentivement.

Tout d'abord, nous créons une formule qui renvoie une valeur de period et ajoute 12:

= ([Période] + 12)

Le résultat est divisé par 13, car l'exercice comporte 13 28 jours.

= (([Période] + 12)/13)

Nous ajoutons 2010, car il s'agit de la première année de la table:

= (([Période] + 12)/13)+ 2010

Pour finir, nous utilisons la fonction INT pour supprimer les fractions du résultat et renvoyer un nombre entier, après division de par 13, comme suit:

=Ent(([période] + 12)/13)+2010

Le résultat est semblable à ce qui suit:

Colonne Période d’exercice

Période dans l'exercice

Cette valeur renvoie le numéro de la période (1 à 13), à partir de la première période complète (débutant un dimanche) dans chaque exercice.

= Si (MOD ([période]; 13); MOD ([période]; 13); 13)

Comme cette formule est un peu plus complexe, nous décrirons celle-ci d'abord dans un langage compréhensible. Cet état de formule divise la valeur de [période] par 13 pour obtenir un numéro de période (1-13) dans l'année. Si ce nombre est 0, retournez 13.

Tout d'abord, nous créons une formule qui renvoie le reste de la valeur de la période de 13. Nous pouvons utiliser le Mod (fonctions mathématiques et trigonométriques) comme suit:

=Mod ([période], 13)

C'est la première fois que vous obtenez le résultat que nous voulons, sauf dans la mesure où la valeur de la période est de 0, car ces dates ne sont pas comprises dans la première année fiscale, comme les cinq premiers jours de notre exemple de table de dates de calendrier. Vous pouvez le faire à l'aide de la fonction si. Dans le cas où notre résultat est 0, nous avons renvoyé 13, comme suit:

=Si(mod ([période]; 13); Mod ([période]; 13); 13)

Le résultat est semblable à ce qui suit:

Colonne Période de l’exercice

Exemple de tableau croisé dynamique

L'image ci-dessous montre un tableau croisé dynamique avec le champ SalesAmount de la table faits ventes dans valeurs et les champs PeriodFiscalYear et PeriodInFiscalYear de la table de dimension Date du calendrier dans lignes. SalesAmount est agrégé pour le contexte par année fiscale et par période de 28 jours de l'exercice.

Exemple tableau croisé dynamique pour l’exercice

Relations

Une fois que vous avez créé une table de dates dans votre modèle de données, pour commencer à parcourir vos données dans les tableaux croisés dynamiques et les rapports, et pour agréger les données sur la base des colonnes de votre table de dimension de date, vous devez créer une relation entre la table de faits et vos données de transaction et table de dates.

Étant donné que vous devez créer une relation basée sur des dates, vous devez vous assurer de créer cette relation entre les colonnes dont les valeurs sont du type de données DateTime (date).

Pour chaque valeur de date dans la table de faits, la colonne de recherche associée dans la table de dates doit contenir des valeurs correspondantes. Par exemple, une ligne (enregistrement de transaction) dans la table de faits de ventes ayant une valeur de 8/15/2012 12:00 AM dans la colonne Clédate doit avoir une valeur correspondante dans la colonne de date associée de la table Date (nom du calendrier). C'est l'une des raisons les plus importantes pour lesquelles vous souhaitez que votre colonne de date dans la table de dates contienne une plage de dates contiguë incluant n'importe quelle date éventuelle dans votre table de faits.

Relations en mode Diagramme

Remarque : Même si la colonne de date de chaque table doit avoir le même type de données (date), le format de chaque colonne n'a pas d'importance.

Remarque : Si Power Pivot ne vous permet pas de créer des relations entre les deux tables, les champs de date risquent de ne pas stocker la date et l'heure au même niveau de précision. En fonction de la mise en forme de la colonne, les valeurs risquent d'avoir la même valeur, mais sont stockées différemment. En savoir plus sur l' utilisation des horaires.

Remarque : Évitez d'utiliser des clés de substitution de type entier dans les relations. Lorsque vous importez des données à partir d'une source de données relationnelle, des colonnes de date et d'heure sont souvent représentées par une clé de remplacement, qui est une colonne entière utilisée pour représenter une date unique. Dans Power Pivot, vous devez éviter de créer des relations à l'aide de clés de date/heure entières, et plutôt utiliser des colonnes qui contiennent des valeurs uniques avec un type de données date. Même si l'utilisation de clés de substitution est recommandée dans les entrepôts de données traditionnels, les clés entières ne sont pas nécessaires dans Power Pivot et peuvent compliquer le regroupement de valeurs dans les tableaux croisés dynamiques selon les périodes de dates différentes.

Si vous obtenez une erreur d'incompatibilité de type lors de la tentative de création d'une relation, c'est probablement parce que la colonne de la table de faits n'est pas du type de données date. Cela peut se produire lorsque Power Pivot ne peut pas convertir automatiquement une variable qui n'est pas une date (généralement un type de données texte) en un type de données date. Vous pouvez toujours utiliser la colonne dans votre table de faits, mais vous devrez convertir les données avec une formule DAX dans une nouvelle colonne calculée. Pour plus d'informations, rePortez-vous à la section conversion des dates du type de données texte vers un type de données date .

Relations multiples

Dans certains cas, il est parfois nécessaire de créer plusieurs relations ou de créer plusieurs tables de dates. Par exemple, s'il existe plusieurs champs de date dans la table de faits de ventes, tels que Clédate, ShipDate et colonne dateretour indiquant, les deux peuvent avoir une relation avec le champ date dans la table de dates du calendrier, mais une seule de celles peut être une relation active. Dans le cas présent, dans la mesure où Clédate représente la date de la transaction et, par conséquent, la date la plus importante, celle-ci sera la plus utilisée comme relation active . Les autres utilisateurs ont des relations inactives.

Le tableau croisé dynamique suivant calcule le total des ventes par année fiscale et par trimestre. Une mesure appelée total des ventes avec la formule total des ventes: = somme ([SalesAmount]), est placée dans les valeurs et les champs l'exercice et FiscalQuarter de la table de dates du calendrier sont placés dans des lignes.

Tableau croisé dynamique Total des ventes par trimestre d’exercice Liste des champs du tableau croisé dynamique

Ce tableau croisé dynamique de défilement direct fonctionne correctement car nous voulons additionner nos ventes totales en fonction de laDate de la transactiondans clédate. Notre mesure total des ventes utilise les dates de Clédate et est additionnée par exercice fiscal et par trimestre fiscal, car il existe une relation entre Clédate dans la table Sales et la colonne Date de la table de dates du calendrier.

Relations inactives

Mais, si nous voulons additionner notre total de ventes sans date de transaction, mais par Date d'expédition? Nous avons besoin d'une relation entre la colonne ShipDate de la table ventes et la colonne Date de la table calendrier. Si ce n'est pas le cas, notre agrégation est toujours basée sur la date de la transaction. Néanmoins, vous pouvez avoir plusieurs relations, même si une seule peut être active, et comme la date de transaction est la plus importante, elle obtient la relation active avec la table de calendrier.

Dans ce cas, la fonction ShipDate possède une relation inactive, de sorte que toutes les formules de mesure créées pour agréger les données en fonction des dates d'expédition doivent spécifier la relation inactive à l'aide de la fonction USERELATIONSHIP .

Par exemple, dans la mesure où il existe une relation inactive entre la colonne ShipDate de la table ventes et la colonne Date de la table calendrier, nous pouvons créer une mesure qui additionne le total des ventes par date d'expédition. Nous utilisons une formule semblable à celle-ci pour spécifier la relation à utiliser:

Total des ventes selon la date d'expédition: = CALCULATE (SUM (ventes [SalesAmount]), USERELATIONSHIP (ventes [DateExpédition], calendrier [date]))

Cette formule indique simplement: calcule une somme pour SalesAmount, mais filtre à l'aide de la relation entre la colonne ShipDate de la table ventes et la colonne Date de la table calendrier.

À présent, si nous créons un tableau croisé dynamique et que vous effectuez une mesure de total des ventes par mesure d'expédition dans valeurs et exercice et mois d'exercice sur les lignes, le même total général est affiché, mais tous les autres montants de somme pour l'exercice et le trimestre fiscal sont différents, car ils sont basés sur la date d'expédition et pas la date de la transaction.

Tableau croisé dynamique Total des ventes par date d’expédition Liste des champs du tableau croisé dynamique

L'utilisation de relations inactives vous permet d'utiliser une seule table de dates, mais elle exige que toutes les mesures (telles que total des ventes par date d'expédition) reréférencent la relation inactive dans la formule. Vous pouvez également utiliser plusieurs tables de dates.

Tables de dates multiples

Un autre moyen de travailler avec plusieurs colonnes de date dans votre table de faits consiste à créer plusieurs tables de dates et à créer des relations actives distinctes entre elles. Examinons de nouveau notre exemple de tableau des ventes. Nous avons trois colonnes pour lesquelles il est possible que nous puissions agréger des données:

  • Une Clédate avec la date de vente de chaque transaction.

  • Un champ ShipDate, avec les date et heure auxquelles les articles vendus ont été expédiés au client.

  • Colonne dateretour indiquant: date et heure de réception d'un ou plusieurs articles renvoyés.

N'oubliez pas que le champ Clédate avec la date de la transaction est le plus important. Nous ferons de notre plus d'agrégations en fonction de ces dates; nous voulons donc certainement une relation entre elle et la colonne Date de la table calendrier. Si vous ne voulez pas créer de relations inactives entre ShipDate et colonne dateretour indiquant et le champ date de la table calendrier, ce qui nécessite des formules de mesure spéciales, nous pouvons créer des tables de dates supplémentaires pour la date d'expédition et la date de retour. Vous pouvez créer des relations actives entre eux.

Relations avec plusieurs tables dans la vue de diagramme

Dans cet exemple, nous avons créé une autre table de dates nommée ShipCalendar. Ce processus implique également la création de colonnes de date supplémentaires et celles de ces colonnes de date dans une table de date différente, et nous voulons les nommer de façon à les différencier des mêmes colonnes dans la table calendrier. Par exemple, nous avons créé des colonnes nommées ShipYear, ShipMonth, ShipQuarter, etc.

Si nous créons notre tableau croisé dynamique et nous avons placé notre mesure total des ventes dans valeurs et ShipFiscalYear et ShipFiscalQuarter sur des lignes, nous voyons les mêmes résultats que nous avons constatés lors de la création d'une relation inactif et d'une date de calcul du total des ventes par date d'expédition.

Tableau croisé dynamique Total des ventes selon la date d’expédition avec calendrier d’expédition Liste de champs de tableau croisé dynamique

Chacune de ces approches nécessite une considération soigneuse. Lors de l'utilisation de plusieurs relations avec une table de dates unique, il est possible que vous deviez créer des mesures spéciales pour le transit de relations inactives à l'aide de la fonction USERELATIONSHIP. En revanche, la création de tables de dates multiples peut prêter à confusion dans une liste de champs, et dans la mesure où vous avez plus de tables dans le modèle de données, elle nécessite davantage de mémoire. Expérimentez ce qui vous convient le mieux.

Propriété table de dates

La propriété table de dates définit les métadonnées nécessaires pour que les fonctions Time Intelligence telles que TOTALYTD, PREVIOUSMONTH et DATESBETWEEN fonctionnent correctement. Lors de l'exécution d'un calcul à l'aide de l'une de ces fonctions, le moteur de formule de Power Pivot sait où accéder aux dates dont il a besoin.

Avertissement : Si cette propriété n'est pas définie, il est possible que des mesures utilisant les fonctions Time Intelligence DAX ne retournent pas de résultats corrects.

Lorsque vous définissez la propriété table de dates, vous spécifiez une table de dates et une colonne de date du type de données Date (date/heure).

Boîte de dialogue Marquer en tant que table de dates

Procédure: définir la propriété table de dates

  1. Dans la fenêtre PowerPivot, sélectionnez la table calendrier .

  2. Dans l'onglet création , cliquez sur marquer en tant que table de dates.

  3. Dans la boîte de dialogue marquer en tant que table de dates, sélectionnez une colonne avec des valeurs uniques et le type de données date.

Utilisation des heures

Toutes les valeurs de date avec un type de données date dans Excel ou SQL Server sont en réalité des nombres. Ce numéro inclut les chiffres qui font référence à une heure. Dans de nombreux cas, c'est le moment pour chaque ligne et minuit. Par exemple, si le champ Clédateheure d'une table de faits de ventes comporte des valeurs telles que 10/19/2010 12:00:00 AM, cela signifie que les valeurs sont au niveau du jour de précision. Si les valeurs du champ Clédateheure contiennent un temps (par exemple, 10/19/2010 8:44:00 AM), cela signifie que les valeurs sont du niveau de précision minute. Les valeurs peuvent également être de la précision de niveau heure ou même du niveau de précision. Le niveau de précision de la valeur d'heure aura un impact important sur la façon dont vous créez votre table de dates et les relations entre celle-ci et votre table de faits.

Vous devez déterminer si vous souhaitez agréger vos données à un niveau de précision journalière ou un niveau de précision. En d'autres termes, vous souhaiterez peut-être utiliser les colonnes de votre table de dates telles que les champs matin, après-midi ou heure comme date dans les zones de lignes, de colonnes ou de filtres du tableau croisé dynamique.

Remarque : Les jours sont la plus petite unité de temps avec laquelle les fonctions Time Intelligence de DAX peuvent travailler. Si vous n'avez pas besoin d'utiliser des valeurs de temps, vous devez réduire la précision de vos données pour utiliser les jours comme unité minimale.

Si vous envisagez de regrouper les données au niveau de l'heure, votre table de dates aura besoin d'une colonne de date avec l'heure incluse. En fait, elle a besoin d'une colonne de date avec une seule ligne pour chaque heure, ou même toutes les minutes de chaque jour, pour chaque année dans la plage de dates. En effet, pour créer une relation entre la colonne Clédateheure dans la table de faits et la colonne de date dans la table de dates, vous devez disposer de valeurs correspondantes. Comme vous pouvez le faire, si vous incluez un grand nombre d'années, il est possible de créer une table de dates très volumineuse.

Dans la plupart des cas, il est préférable de n'agréger vos données qu'à la journée. En d'autres termes, vous utiliserez des colonnes comme année, mois, semaine ou jour de la semaine comme champs dans les zones de lignes, de colonnes ou de filtres d'un tableau croisé dynamique. Dans ce cas, la colonne Date de la table de dates ne doit contenir qu'une seule ligne pour chaque jour d'une année, comme décrit précédemment.

Si votre colonne de date inclut un niveau de précision de l'heure, mais que vous n'en êtes pas le seul niveau, pour créer la relation entre la table de faits et la table de dates, vous devrez peut-être modifier votre table de faits en créant une nouvelle colonne qui tronque les valeurs de la date c olumn sur une valeur journalière. En d'autres termes, convertissez une valeur telle que 10/19/2010 8:44:00AM vers 10/19/2010 12:00:00 AM. Vous pouvez ensuite créer la relation entre cette nouvelle colonne et la colonne Date dans la table de dates, car les valeurs correspondent.

Examinons un exemple. Cette image montre une colonne Clédateheure dans la table de faits ventes. Toutes les agrégations correspondant aux données de cette table doivent avoir uniquement le niveau jour, en utilisant les colonnes de la table de dates du calendrier (par exemple, année, mois, trimestre, etc.). La durée incluse dans la valeur n'est pas pertinente, uniquement la date réelle.

Colonne CléDateHeure

Étant donné que nous n'avons pas besoin d'analyser ces données dans le niveau de temps, nous n'avons pas besoin de la colonne Date de la table de dates du calendrier pour inclure une ligne pour chaque heure et chaque minute de chaque jour de chaque année. Par conséquent, la colonne Date de notre table de dates ressemble à ceci:

Colonne de date dans Power Pivot

Pour créer une relation entre la colonne Clédateheure dans la table Sales et la colonne Date de la table calendrier, nous pouvons créer une colonne calculée dans la table de faits de vente et utiliser la fonction tronque pour tronquer la valeur de date et d'heure dans le clédateheure colonne en une valeur de date qui correspond aux valeurs de la colonne Date de la table calendrier. Notre formule se présente comme suit:

= TRONque ([Clédateheure], 0)

Cela nous permet de créer une colonne (nommée Clédate) avec la date de la colonne Clédateheure et une heure de 12:00:00 AM pour chaque ligne:

Colonne CléDate

Nous pouvons à présent créer une relation entre cette nouvelle colonne (Clédate) et la colonne Date de la table calendrier.

De la même façon, nous pouvons créer une colonne calculée dans la table Sales qui réduit la précision de la colonne Clédateheure en niveau d'heure de précision. Dans ce cas, la fonction TRONque ne fonctionne pas, mais il est toujours possible d'utiliser d'autres fonctions de date et d'heure DAX pour extraire et recréer une nouvelle valeur au niveau de précision de l'heure. Nous pouvons utiliser une formule semblable à celle-ci:

= DATE (YEAR ([Clédateheure]), MONTH ([Clédateheure]), DAY ([Clédateheure])) + TIME (HOUR ([Clédateheure]), 0, 0)

La nouvelle colonne se présente comme suit:

Colonne CléDateHeure

Si votre colonne de date de la table de dates comporte des valeurs au niveau de précision de l'heure, nous pouvons créer une relation entre elles.

Rendre les dates plus exploitables

Bon nombre de colonnes de date créées dans votre table de dates sont nécessaires pour les autres champs, mais ce n'est pas tout ce qui est utile dans une analyse. Par exemple, le champ Clédate de la table Sales que nous avons fait référence à cet article est important, car pour chaque transaction, cette transaction est enregistrée comme ayant lieu à une date et une heure spécifiques. Mais d'un point de vue Analysis et de création de rapports, ce n'est pas le bon moment car il ne peut pas être utilisé comme un champ de ligne, de colonne ou de filtre dans un tableau ou un rapport de tableau croisé dynamique.

De même, dans notre exemple, la colonne Date de la table calendrier est très utile et essentielle dans le fait que vous ne pouvez pas l'utiliser comme une dimension dans un tableau croisé dynamique.

Pour conserver plus facilement les tables et les colonnes, et pour faciliter la navigation dans les listes de champs de rapport de tableau croisé dynamique ou Power View, il est important de masquer les colonnes inutiles d'outils clients. Vous pouvez également masquer certaines tables. La table jours fériés présentée ci-dessous contient les dates de vacances qui sont importantes pour certaines colonnes de la table calendrier, mais vous ne pouvez pas utiliser les colonnes date et vacances dans la table jours fériés comme les champs d'un tableau croisé dynamique. Là encore, pour faciliter la navigation dans les listes de champs, vous pouvez masquer tout le tableau des jours fériés.

Un autre aspect important de l'utilisation des dates est la Convention d'affectation de noms. Vous pouvez nommer les tables et les colonnes dans Power Pivot selon vos besoins. Tout en gardant à l'esprit, en particulier si vous partagez votre classeur avec d'autres utilisateurs, une bonne Convention d'appellation facilite l'identification des tables et des dates, non seulement dans les listes de champs, mais également dans PowerPivot et dans les formules DAX.

Après avoir une table de dates dans votre modèle de données, vous pouvez commencer à créer des mesures qui vous aideront à tirer le meilleur parti de vos données. Certains pourront être aussi simples que le total des ventes de l'année en cours, et les autres pourront être plus complexes, car vous devrez filtrer sur une plage de dates uniques spécifiques. Pour plus d'informations, voir mesures dans Power Pivot et fonctions Time Intelligence.

Annexe

Convertir des dates du type de données texte vers le type de données date

Dans certains cas, une table de faits incluant des données de transaction peut contenir des dates de type de données texte. C'est-à-dire qu'une date qui s'affiche sous la forme 2012-12-04T11:47:09 n'est en fait pas une date ou qu'il n'y a pas de date de fonctionnement sur Power Pivot. Il s'agit simplement du texte qui ressemble à une date. Pour créer une relation entre une colonne de date dans la table de faits et une colonne de date dans une table de dates, les deux colonnes doivent avoir le type de données Date .

En règle générale, lorsque vous tentez de modifier le type de données d'une colonne de dates qui sont de type données texte en type de données date, Power Pivot peut interpréter les dates et les convertir en un type de données date réelle. S'il n'est pas possible d'effectuer une conversion de type de données dans Power Pivot, vous obtiendrez une erreur d'incompatibilité de type.

Toutefois, vous pouvez toujours convertir les dates en un type de données date réelle. Vous pouvez créer une colonne calculée et utiliser une formule DAX pour analyser l'année, le mois, le jour, l'heure, etc., à partir des chaînes de texte, puis les concaténer dans le cadre de la lecture d'une telle valeur dans Power Pivot.

Dans cet exemple, nous avons importé une table de faits intitulée Sales dans Power Pivot. Elle contient une colonne nommée DateTime. Les valeurs apparaissent comme suit:

Colonne Date/Heure dans une table de faits

Si nous examinons le type de données du groupe mise en forme sous l'onglet Accueil de Power Pivot, nous voyons qu'il s'agit du type de données texte.

Type de données dans le ruban

Il n'est pas possible de créer une relation entre la colonne DateHeure et la colonne Date de notre table de dates, car les types de données ne correspondent pas. Si nous essayons de changer le type de données en Date, nous obtenons une erreur d'incompatibilité de type:

Erreur d’incompatibilité

Dans ce cas, Power Pivot n'était pas en mesure de convertir le type de données de texte en date. Nous pouvons toujours utiliser cette colonne, mais pour la convertir en un type de données date réelle, nous devons créer une nouvelle colonne qui analyse le texte et le recrée en une valeur Power Pivot peut définir un type de données date.

Ne perdez pas de temps à partir de la section utilisation de l'heure plus haut dans cet article. à moins que votre analyse ne soit nécessaire, vous devez convertir les dates de votre table de faits en un niveau de précision de la journée. À l'esprit, nous voulons que les valeurs de notre nouvelle colonne soient au niveau du jour de la précision (à l'exception du temps). Nous pouvons convertir les valeurs de la colonne DateHeure en un type de données date et supprimer le niveau de précision de l'heure avec la formule suivante:

= DATE (gauche ([DateHeure]; 4); STXT ([DateHeure]; 6; 2); STXT ([DateHeure]; 9; 2))

Cela nous permet de créer une colonne (dans ce cas, date nommée). Power Pivot détecte les valeurs en tant que dates et définit automatiquement le type de données sur date.

Colonne de date d’une table de faits

Si nous voulons préserver le niveau de précision de l'heure, il suffit d'étendre la formule afin d'inclure les heures, les minutes et les secondes.

= DATE (gauche ([DateHeure]; 4); STXT ([DateHeure]; 6; 2); STXT ([DateHeure]; 9; 2)) +

TEMPS (STXT ([DateHeure], 12, 2), STXT ([DateHeure], 15; 2), STXT ([DateHeure]; 18; 2))

À présent que nous avons une colonne Date du type de données date, nous pouvons créer une relation entre elle et une colonne de date dans une date.

Ressources supplémentaires

Dates dans Power Pivot

Calculs dans Power Pivot

Démarrage rapide : découvrir les fondamentaux de DAX en 30 minutes

Référence des expressions d'analyse des données

Centre de ressources de Dax

Développez vos compétences dans Office
Découvrez des formations
Accédez aux nouvelles fonctionnalités en avant-première
Rejoignez le programme Office Insider

Ces informations vous ont-elles été utiles ?

Nous vous remercions pour vos commentaires.

Merci pour vos commentaires. Il serait vraisemblablement utile pour vous de contacter l’un de nos agents du support Office.

×