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

Créer une table de dates dans Excel

Vous pouvez créer une table de dates Excel puis la copier dans une nouvelle table du modèle de données. Cette solution est très simple à faire et vous offre une grande flexibilité.

Lorsque vous créez une table de dates dans Excel, vous commencez par une seule colonne avec une plage de dates contiguës. Vous pouvez ensuite créer des colonnes supplémentaires (par exemple, Année, Trimestre, Mois, Année fiscale, Période, etc.) dans la feuille de calcul Excel en utilisant des 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 Ajout de nouvelles colonnes de date à la section Table de dates plus loin dans cet article.

Ajout de nouvelles colonnes de date à la table de dates

Une table de dates avec une seule colonne de date qui possède une ligne pour chaque jour pour chaque année est importante pour définir toutes les dates d’une plage de dates. Elle est également nécessaire pour créer une relation entre la table de faits et la table de dates. Mais cette colonne de date unique avec une ligne pour chaque jour n’est pas utile lors de l’analyse par dates d’un tableau croisé dynamique ou d Power View rapport. Vous souhaitez que votre table de dates inclue des colonnes qui vous aident à agréger vos données pour une plage ou un groupe de dates. Par exemple, vous pouvez calculer la somme des montants des ventes par mois ou par trimestre, ou vous pouvez créer une mesure qui calcule la croissance d’une année à l’autre. Dans chacun de ces cas, votre table de dates a besoin de colonnes d’année, de mois ou de trimestres qui vous permettent d’agréger vos données pour cette période.

Si vous avez importé votre table de dates à partir d’une source de données relationnelle, elle peut déjà inclure les différents types de colonnes de date que vous souhaitez. Dans certains cas, vous pouvez modifier certaines de ces colonnes ou créer des colonnes de date supplémentaires. C’est particulièrement vrai si vous créez votre propre table de dates Excel et que vous la copiez dans le modèle de données. Heureusement, la création de colonnes de date dans Power Pivot est relativement simple avec les fonctions de date et d’heure dans DAX.

Fonctions de date et d’heure DAX

Si vous avez déjà travaillé avec des fonctions de date et d’heure dans Excel, vous serez probablement familiarisé avec les fonctions de date et d’heure. Bien que ces fonctions soient similaires à celles de Excel, il existe quelques différences importantes :

• Les fonctions DAX Date et Heure utilisent un type de données datetime.

• Ils peuvent prendre des valeurs d’une colonne en tant qu’argument.

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

Ces fonctions sont souvent utilisées lors de la création de colonnes de date personnalisées dans une table de dates. Il est donc important qu’elles soient compréhensibles. Nous utiliserons un certain nombre de ces fonctions pour créer des colonnes pour Year, Quarter, FiscalMonth, etc.

DAX inclut les fonctions date et heure suivantes :

• DATE

• DATEVAL

• NEXTDAY

• MOIS.DEERER

• FIN.MOIS

• HEURE

• MINUTE

• MONTH

• MAINTENANT

• SECOND

• TIME

• TEMPSVAL

• AUJOURD’HUI

• WEEKDAY

• WEEKNUM

• YEAR

• YEARFRAC

Il existe également de nombreuses autres fonctions DAX que vous pouvez utiliser dans vos formules. Par exemple, bon nombre des formules décrites ici utilisent des fonctions mathématiques et trigonométriques telles que MOD et TRONQUÉ, des fonctions logiques telles que SI et des fonctions de texte telles que FORMAT Pour plus d’informations sur les autres fonctions DAX, voir la section Ressources supplémentaires plus loin dans cet article.

Exemples de formules pour une année civile

Les exemples suivants décrivent les formules utilisées pour 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ës du 01/01/2010 au 31/12/2016.

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écifieons la colonne Date comme premier argument, puis le format. nous souhaitons que notre nom de mois affiche tous les caractères. Nous utilisons donc « mmmm ». Notre résultat ressemble à ceci :

Si vous voulez renvoyer le nom du mois abrégé en trois lettres, nous utiliserions « mmm » dans l’argument format.

Jour de la semaine

=FORMAT([date],"ddd »)

Dans cette formule, nous utilisons la fonction FORMAT pour obtenir le nom du jour. Comme nous voulons simplement un nom de jour abrégé, nous spécifieons « jdd » dans l’argument de mise en forme.

Exemple de tableau croisé dynamique

Une fois que vous avez des champs pour des dates telles que Année, Trimestre, Mois, etc., vous pouvez les utiliser dans un tableau croisé dynamique ou un rapport. Par exemple, l’image suivante illustre le champ SalesAmount de la table de faits Sales dans VALUES, et l’année et le trimestre de la table de dimension Calendrier dans LIGNES. SalesAmount est agrégé pour le contexte des exercices annuels et trimestres.

Exemples de formules pour un exercice fiscal

Jours fériés ou dates spéciales

Vous pouvez inclure une colonne de dates qui indique que certaines dates sont des jours fériés ou d’autres dates spéciales. Par exemple, vous pouvez additioner les totaux des ventes du jour Nouvel an en ajoutant un champ Jours fériés à un tableau croisé dynamique, en tant que slicer, ou en filtrant. Dans d’autres cas, vous pouvez exclure ces dates d’autres colonnes de date ou dans une mesure.

Il est très simple d’inclure les jours fériés ou les jours spéciaux. Vous pouvez créer une table dans Excel les dates que vous voulez inclure. Vous pouvez ensuite copier ou utiliser l’ajout 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 y font référence peuvent utiliser la fonction VALEUR.RECHERCHE pour renvoyer des valeurs.

Voici un exemple de tableau créé dans Excel qui inclut des jours fériés à ajouter à la table de dates :

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

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

Examinons cette formule plus attentivement.

La fonction VALEUR.RECHERCHE vous aide à obtenir des valeurs dans la colonne Jours fériés de la table Jours fériés. Dans le premier argument, nous spécifieons la colonne dans laquelle la valeur du résultat sera spécifiée. Nous spécifieons la colonne Jours fériés dans la table Jours fériés, car il s’agit de la valeur que nous souhaitons retourner.

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

Nous spécifieons ensuite le deuxième argument, la colonne de recherche qui présente les dates à rechercher. Nous spécifieons la colonne Date dans la table Jours fériés, comme ceci :

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

Enfin, nous allons spécifier la colonne de la table Calendrier qui présente les dates que vous souhaitez rechercher dans la table Vacances. Bien entendu, il s’agit de la colonne Date dans la table Calendrier.

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

La colonne Jours fériés renseance le nom de chaque ligne dont la valeur de date correspond à une date du tableau Jours fériés.

Calendrier personnalisé - treize périodes de quatre semaines

Certaines organisations, telles que le service de vente au détail ou de restauration, font souvent des rapports sur différentes périodes (par exemple, treize périodes de quatre semaines). Avec un calendrier de 14 semaines, chaque période est de 28 jours. par conséquent, chaque période contient quatre lundis, quatre mardis, quatre mercredis, etc. Chaque période contient le même nombre de jours. En règle générale, les jours fériés tombent sur la même période chaque année. Vous pouvez choisir de commencer un période n’importe quel jour de la semaine. Tout comme pour 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’année fiscale. Dans ce cas, l’exercice commence le 01/07.

Point

La période de ce calendrier personnalisé contient 28 jours et elle commence toujours un dimanche. Cette colonne retourne 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 à partir de la colonne Semaine sous la mesure d’un sont des valeurs totales, comme ceci :

=ENT([Semaine])

Ajoutez ensuite 3 à cette valeur, comme ceci :

=ENT([Semaine]+3)

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

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

Le résultat ressemble à ceci :

Exercice période

Cette valeur renvoie l’année fiscale pour une période donnée.

=ENT(([Point]+12)/13)+2008

Examinons cette formule plus attentivement.

Tout d’abord, nous créons une formule qui renvoie une valeur à partir de période et ajoute 12 :

= ([Point]+12)

Nous divisez le résultat par 13, car l’année fiscale compte treize périodes de 28 jours :

=(([Point]+12)/13)

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

=(([Point]+12)/13)+2010

Enfin, nous utilisons la fonction ENT pour supprimer une fraction du résultat et renvoyer un nombre entier, lorsqu’il est divisé par 13, comme ceci :

=ENT(([Point]+12)/13)+2010

Le résultat ressemble à ceci :

Période de l’exercice fiscal

Cette valeur renvoie le numéro de période, 1 à 13, en commençant par la première période complète (commençant le dimanche) de chaque année fiscale.

=SI(MOD([Période],13), MOD([Période],13),13)

Cette formule est un peu plus complexe. Nous allons donc la décrire en premier dans une langue que nous comprenons mieux. Cette formule indique, divisez la valeur de [période] par 13 pour obtenir un nombre de période (1-13) dans l’année. Si ce nombre est 0, renvoyer 13.

Tout d’abord, nous créons une formule qui renvoie le reste de la valeur de la période par 13. Vous pouvez utiliser la fonction MOD (fonctions mathématiques et trigonométriques) comme ceci :

=MOD([Période],13)

Cela nous donne, pour l’essentiel, le résultat voulu, sauf que la valeur période est 0, car ces dates ne tombent pas dans le premier exercice, comme dans les cinq premiers jours de notre exemple de table de dates calendrier. Vous pouvez faire cela avec une fonction SI. Dans le cas où notre résultat est 0, nous en renvoyons 13, comme ceci :

=SI(MOD([Période],13),MOD([Période],13),13)

Le résultat ressemble à ceci :

Relations multiples

Dans certains cas, il peut être 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 Sales, par exemple DateKey, ShipDate et ReturnDate, ils peuvent tous avoir des relations avec le champ Date dans la table Date du calendrier, mais un seul d’entre eux peut être une relation active. Dans ce cas, étant donné que DateKey représente la date de la transaction et, par conséquent, la date la plus importante, il est préférable que cette date corresponde à la relation active. Les autres ont des relations inactives.

Le tableau croisé dynamique suivant calcule le total des ventes par exercice et trimestre d’exercice. Une mesure nommée Total Sales, avec la formule Total Sales:=SUM([SalesAmount]), est placée dans VALUES et les champs FiscalYear et FiscalQuarter de la table de dates de calendrier sont placés dans LIGNES.

Ce tableau croisé dynamique simple fonctionne correctement, car vous voulez additioner nos ventes totales à la date de latransaction dans DateKey. Notre mesure Total Sales utilise les dates dans DateKey et est additionée par année fiscale et trimestre fiscal, car il existe une relation entre DateKey dans la table Sales et la colonne Date dans la table Date du calendrier.

Relations inactives

Que se passe-t-il si vous souhaitez additioner nos ventes totales non par date de transaction, mais par date d’expédition? Il est nécessaire d’avoir une relation entre la colonne Date Expédition de la table Ventes et la colonne Date de la table Calendrier. Si nous ne créons pas cette relation, nos agrégations sont toujours basées sur la date de transaction. Toutefois, nous pouvons avoir plusieurs relations, même si une seule peut être active, et étant donné que la date de transaction est la plus importante, elle obtient la relation active avec la table Calendrier.

Dans ce cas, ShipDate a une relation inactive, aussi toute formule de mesure créée pour agréger les données en fonction des dates d’expédition doit spécifier la relation inactive à l’aide de la fonction USERELATIONSHIP.

Par exemple, étant donné qu’il existe une relation inactive entre la colonne Date Expédition dans la table Ventes et la colonne Date dans la table Calendrier, nous pouvons créer une mesure qui additionnera les ventes totales par date d’expédition. Nous utilisons une formule comme celle-ci pour spécifier la relation à utiliser :

Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

Cette formule indique simplement : Calculez une somme pour SalesAmount, mais filtrez en utilisant la relation entre la colonne ShipDate de la table Sales et la colonne Date de la table Calendrier.

Maintenant, si nous créons un tableau croisé dynamique et que nous inséons une mesure Total Sales by Ship Date dans VALUES et que l’année fiscale et le trimestre fiscal sur LIGNES sont des lignes, nous voyons le même total global, mais tous les autres montants de somme pour l’exercice et le trimestre fiscal sont différents parce qu’ils sont basés sur la date d’expédition et non sur la date de transaction.

L’utilisation de relations inactives ne permet d’utiliser qu’une seule table de dates, mais exige que les mesures (telles que Les ventes totales par date d’expédition) référencent la relation inactive dans sa formule. Il existe une autre solution, à laquelle vous pouvez faire appel, c’est-à-dire utiliser plusieurs tables de dates.

Plusieurs tables de dates

Une autre façon d’utiliser plusieurs colonnes de dates dans votre table de faits consiste à créer plusieurs tables de dates et à créer des relations actives distinctes entre elles. Examinons à nouveau notre exemple de table Ventes. Nous avons trois colonnes avec des dates sur des données que nous pourrions avoir envie d’agréger :

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

• DateEx expédiée au client, avec la date et l’heure de livraison des articles vendus.

• Date Renvoyée : avec la date et l’heure de réception d’un ou plusieurs éléments renvoyés.

N’oubliez pas que le champ DateKey avec la date de transaction est le plus important. Nous allons faire la plupart de nos agrégations sur la base de ces dates, donc nous souhaitons sans doute avoir une relation entre elle et la colonne Date dans la table Calendrier. Si nous ne voulons pas créer de relations inactives entre DateÉ shipDate et ReturnDate et le champ Date dans la table Calendrier, ce qui nécessite des formules de mesure spéciale, nous pouvons créer des tables de dates supplémentaires pour la date d’expédition et la date de retour. Nous pouvons ensuite créer des relations actives entre elles.

Dans cet exemple, nous avons créé une autre table de dates nommée ShipCalendar. Cela implique également de créer des colonnes de date supplémentaires et, étant donné que ces colonnes de date se trouveraient dans une autre table de dates, nous souhaitons 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 insérons notre mesure Total Sales dans VALUES, et ShipFiscalYear et ShipFiscalQuarter sur ROWS, nous voyons les mêmes résultats que nous avons vus lorsque nous avons créé une relation inactive et un champ calculé Total Sales by Ship Date spécial.

Chacune de ces approches nécessite une attention particulière. Lorsque vous utilisez plusieurs relations avec une seule table de dates, vous de pouvez être invité à créer des mesures spéciales qui transitent des relations inactives à l’aide de la fonction USERELATIONSHIP. En revanche, la création de plusieurs tables de dates peut prêter à confusion dans une liste de champs. Étant donné que le modèle de données compte davantage de tables, cela nécessitera davantage de mémoire. Testez ce qui vous fonctionne le mieux.

Comment définir la propriété Table de dates

1. Dans la PowerPivot, sélectionnez la table Calendrier.

2. Sous 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.

Conversion de dates de type de données texte en type de données de date

Dans certains cas, une table de faits avec des données de transaction peut contenir des dates de type texte. Autrement dit, une date qui apparaît comme 2012-12-04T11:47:09 n’est en fait pas une date du tout, ou au moins pas le type de date que Power Pivot peut comprendre. Il ne s’agit que de textes qui ressemblent à 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 être du 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 du type de données texte en type de données de date, Power Pivot peut interpréter les dates et les convertir automatiquement en type de données date vrai. Si Power Pivot ne peut pas convertir le type de données, vous recevez une erreur d’inversion de type.

En revanche, vous pouvez toujours convertir les dates en un type de données de date réel. Vous pouvez créer une colonne calculée et utiliser une formule DAX pour l’année, le mois, le jour, l’heure, etc. à partir des chaînes de texte, puis les concaténer de manière à ce que Power Pivot puisse lire une véritable date.

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

Si vous consultez le type de données dans l’onglet Accueil de Power Pivot pour la mise en forme, nous voyons qu’il s’agit du type de données Texte.

Nous ne pouvons pas créer de relation entre les colonnes DateTime et Date dans notre table de dates, car les types de données ne correspondent pas. Si nous essayons de modifier le type de données en Date,nous recevons une erreur d’inserreur de type :

Dans ce cas, Power Pivot n’a pas pu convertir le type de données de texte à ce jour. Nous pouvons toujours utiliser cette colonne, mais pour obtenir un véritable type de données date, vous devez créer une colonne qui analyse le texte et le crée à nouveau en une valeur que Power Pivot peut créer comme type de données Date.

N’oubliez pas qu’à partir de la section Travailler avec le temps plus tôt dans cet article ; sauf si cela est nécessaire, vous devez convertir les dates de votre table de faits en niveau de précision de jour. En ayant cela à l’esprit, nous souhaitons que les valeurs de notre nouvelle colonne soient au niveau de précision du jour (en excluant le temps). Nous pouvons à la fois convertir les valeurs de la colonne DateTime en type de données de date et supprimer le niveau de précision de l’heure avec la formule suivante :

=DATE(GAUCHE([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))

Cela nous donne une nouvelle colonne (dans ce cas, nommée Date). Power Pivot détecte même les valeurs qui doivent être des dates et définit automatiquement le type de données Date.

Pour conserver le niveau de précision dans l’heure, il suffit d’étendre la formule afin d’inclure les heures, les minutes et les secondes.

=DATE(GAUCHE([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +

TEMPS(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))

À présent que nous avons une colonne Date du type de données Date, nous pouvons créer une relation entre celle-ci 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 DAX