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

Create une table de dates dans Excel

Vous pouvez créer une table de dates dans Excel, puis la copier dans une nouvelle table du modèle de données. C’est vraiment assez facile à faire et cela vous donne beaucoup de flexibilité.

Lorsque vous créez une table de dates dans Excel, vous commencez par une seule colonne avec une plage de dates contiguë. Vous pouvez ensuite créer des colonnes supplémentaires telles que Année, Trimestre, Mois, Année fiscale, 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 sous forme de 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 table de dates plus loin dans cet article.

Ajout de nouvelles colonnes de date à la table de dates

Une table de dates avec une colonne de date unique qui a une ligne par 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. Toutefois, cette colonne de date unique avec une ligne pour chaque jour n’est pas utile lors de l’analyse par dates dans un rapport de tableau croisé dynamique ou Power View. 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 additionner les montants des ventes par mois ou trimestre, ou 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 année, mois ou trimestre 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 souhaités. Dans certains cas, vous pouvez modifier certaines de ces colonnes ou créer des colonnes de date supplémentaires. Cela 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 Power Pivot est assez facile avec les fonctions date et heure dans DAX.

Fonctions date et heure DAX

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

• Les fonctions Date et Heure DAX 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 retourner 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 de les comprendre. Nous allons utiliser 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

• DATEVALUE

• LENDEMAIN

• EDATE

• EOMONTH

• HEURE

• MINUTE

• MOIS

• MAINTENANT

• DEUXIÈME

• TEMPS

• VALEUR TIMEVALUE

• AUJOURD'HUI

• SEMAINE

• WEEKNUM

• ANNÉE

• YEARFRAC

Vous pouvez également utiliser de nombreuses autres fonctions DAX dans vos formules. Par exemple, la plupart des formules décrites ici utilisent des fonctions mathématiques et trigonométriques comme MOD et TRUNC, des fonctions logiques comme SI et des fonctions de texte comme FORMAT Pour plus d’informations sur d’autres fonctions DAX, consultez 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 contiguë de dates comprise entre le 1/1/2010 et le 31/12/2016.

Month Name

=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, donc nous utilisons « mmmm ». Notre résultat ressemble à ceci :

Si nous voulons retourner le nom de mois abrégé en trois lettres, nous utilisons « 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. Étant donné que nous voulons simplement un nom de jour abrégé, nous spécifions « ddd » dans l’argument format.

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 montre 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 ROWS. SalesAmount est agrégé pour le contexte de l’année et du trimestre.

Exemples de formules pour un exercice

Jours fériés ou dates spéciales

Vous pouvez inclure une colonne de date qui indique que certaines dates sont des jours fériés ou une autre date spéciale. Par exemple, vous pouvez additionner les totaux des ventes pour le Jour de l’an en ajoutant un champ Jour férié à un tableau croisé dynamique, en tant que segment ou filtre. Dans d’autres cas, vous pouvez exclure ces dates d’autres colonnes de date ou d’une mesure.

Inclure des jours fériés ou des jours spéciaux est assez simple. Vous pouvez créer un tableau dans Excel contenant les dates que vous souhaitez inclure. Vous pouvez ensuite copier ou utiliser 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 y font référence peuvent utiliser la fonction LOOKUPVALUE pour retourner 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 Congé et utilisons une formule comme celle-ci :

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

Examinons cette formule plus attentivement.

Nous utilisons la fonction LOOKUPVALUE pour obtenir des valeurs à partir de la colonne Congés de la table Jours fériés. Dans le premier argument, nous spécifions la colonne où sera notre valeur de résultat. Nous spécifions la colonne Congé dans la table Jours fériés , car il s’agit de la valeur que nous voulons retourner.

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

Nous spécifions ensuite le deuxième 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 :

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

Enfin, nous spécifions la colonne dans notre table Calendrier qui contient les dates que nous voulons rechercher dans la table Congés . Il s’agit bien sûr de la colonne Date dans la table Calendrier .

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

La colonne Jour férié renvoie le nom du jour férié pour chaque ligne dont la valeur de date correspond à une date dans la table Jours fériés.

Calendrier personnalisé : treize périodes de quatre semaines

Certaines organisations, comme la vente au détail ou la restauration, font souvent rapport sur des périodes différentes, comme treize périodes de quatre semaines. Avec un calendrier de treize périodes de quatre semaines, chaque période est de 28 jours ; par conséquent, chaque période contient quatre lundis, quatre mardis, quatre mercredis, et ainsi de suite. Chaque période contient le même nombre de jours et, en règle générale, les jours fériés se situent dans la même période chaque année. Vous pouvez choisir de commencer une période n’importe quel jour de la semaine. Tout comme pour les dates d’un calendrier ou d’un exercice, 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.

Point

La période de ce calendrier personnalisé contient 28 jours et commence toujours un dimanche. Cette colonne retourne le numéro de la période commençant par le premier dimanche du premier exercice.

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

Examinons cette formule plus attentivement.

Tout d’abord, nous créons une formule qui retourne une valeur de la colonne Week sous forme d’entier, comme suit :

=INT([Semaine])

Ajoutez ensuite 3 à cette valeur, comme suit :

=INT([Semaine]+3)

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

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

Le résultat ressemble à ceci :

Exercice fiscal de la période

Cette valeur retourne l’année fiscale d’une période.

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

Examinons cette formule plus attentivement.

Tout d’abord, nous créons une formule qui retourne une valeur de Point et ajoute 12 :

= ([Période]+12)

Nous divisons le résultat par 13, car il y a treize périodes de 28 jours dans l’exercice financier :

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

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

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

Enfin, nous utilisons la fonction INT pour supprimer toute fraction du résultat et retourner un nombre entier, divisé par 13, comme suit :

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

Le résultat ressemble à ceci :

Période dans FiscalYear

Cette valeur retourne le nombre de périodes, 1 à 13, en commençant par la première période complète (à compter du dimanche) de chaque exercice.

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

Cette formule étant un peu plus complexe, nous allons d’abord la décrire dans un langage 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, retournez 13.

Tout d’abord, nous créons une formule qui retourne le reste de la valeur de Period par 13. Nous pouvons utiliser mod ( fonctions mathématiques et trigonométriques) comme suit :

=MOD([Période],13)

Ceci, pour la plupart, nous donne le résultat souhaité, sauf si la valeur de 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. Nous pouvons nous en occuper avec une fonction SI. Si notre résultat est 0, nous renvoyons 13, comme suit :

=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, tels que DateKey, ShipDate et ReturnDate, ils peuvent tous avoir des relations avec le champ Date de la table Date du calendrier, mais un seul de ces champs peut être une relation active. Dans ce cas, étant donné que DateKey représente la date de la transaction, et donc la date la plus importante, il est préférable de servir de relation active . Les autres ont des relations inactives.

Le tableau croisé dynamique suivant calcule le total des ventes par année fiscale et trimestre fiscal. Une mesure nommée Total Sales, avec la formule Total Sales :=SUM([SalesAmount]) est placée dans VALEURS, et les champs FiscalYear et FiscalQuarter de la table de dates Calendrier sont placés dans ROWS.

Ce tableau croisé dynamique simple fonctionne correctement, car nous voulons additionner nos ventes totales par date de transaction dans DateKey. Notre mesure Total Sales utilise les dates dans DateKey et est additionnée par année fiscale et trimestre d’exercice, car il existe une relation entre DateKey dans la table Sales et la colonne Date dans la table Date de calendrier.

Relations inactives

Mais que se passe-t-il si nous voulions additionner nos ventes totales non par date de transaction, mais par date d’expédition ? Nous avons besoin d’une relation entre la colonne ShipDate de la table Sales et la colonne Date dans 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 comme 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. Par conséquent, toute formule de mesure créée pour agréger des 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 ShipDate dans la table Sales et la colonne Date dans la table Calendrier, nous pouvons créer une mesure qui additionne le total des ventes 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 dans la table Sales et la colonne Date dans la table Calendar.

À présent, si nous créons un tableau croisé dynamique et que nous mettons la mesure Total Sales by Ship Date dans VALEURS, et Fiscal Year and Fiscal Quarter on ROWS, nous voyons le même total général, mais tous les autres montants pour l’année fiscale et le trimestre fiscal sont différents, car ils sont basés sur la date d’expédition et non sur la date de transaction.

L’utilisation de relations inactives vous permet d’utiliser une seule table de dates, mais elle nécessite que toutes les mesures (comme Total Sales by Ship Date) référencent la relation inactive dans sa formule. Il existe une autre alternative, c’est-à-dire utiliser plusieurs tables de dates.

Tables de dates multiples

Une autre façon d’utiliser 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 à nouveau notre exemple de table Sales. Nous avons trois colonnes avec des dates sur lesquelles nous pourrions vouloir agréger des données :

• DateKey avec la date de vente de chaque transaction.

• ShipDate : avec la date et l’heure auxquelles les articles vendus ont été expédiés au client.

• ReturnDate : avec la date et l’heure de réception d’un ou de plusieurs éléments retournés.

N’oubliez pas que le champ DateKey avec la date de transaction est le plus important. Nous allons effectuer la plupart de nos agrégations en fonction de ces dates. Nous souhaitons donc certainement une relation entre celle-ci et la colonne Date de la table Calendrier. Si nous ne voulons pas créer de relations inactives entre ShipDate et ReturnDate et le champ Date dans la table Calendrier, ce qui nécessite des formules de mesures spéciales, 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 eux.

Dans cet exemple, nous avons créé une autre table de dates nommée ShipCalendar. Bien sûr, cela signifie également créer des colonnes de date supplémentaires, et étant donné que ces colonnes de date se trouvent dans une table de dates différente, nous voulons les nommer de manière à les différencier des mêmes colonnes de 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 placez notre mesure Total Sales dans VALEURS, et ShipFiscalYear et ShipFiscalQuarter sur ROWS, nous voyons les mêmes résultats que lorsque nous avons créé une relation inactive et un champ calculé Total Sales by Ship Date.

Chacune de ces approches nécessite une attention particulière. Lorsque vous utilisez plusieurs relations avec une seule table de dates, vous devrez peut-être créer des mesures spéciales qui transitent par 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, et comme vous avez plus de tables dans le modèle de données, cela nécessite plus de mémoire. Expérimentez ce qui vous convient le mieux.

Guide pratique pour définir la propriété Table de dates

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

2. Sous l’onglet Création , cliquez sur Marquer comme table de dates.

3. Dans la boîte de dialogue Marquer comme 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 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 sous la forme 2012-12-04T11 :47 :09 n’est en fait pas une date du tout, ou du moins pas le type de date que Power Pivot peut comprendre. C’est vraiment juste du texte qui se lit comme 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 essayez de modifier le type de données d’une colonne de dates de type texte en type de données date, Power Pivot peut interpréter les dates et les convertir automatiquement en type de données date réelle. Si Power Pivot ne peut pas effectuer une conversion de type de données, vous obtiendrez une erreur d’incompatibilité de type.

Toutefois, vous pouvez toujours convertir les dates en un type de données date true. 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 la concaténer de nouveau de manière à ce que Power Pivot puisse lire comme une date vraie.

Dans cet exemple, nous avons importé une table de faits nommée Sales dans Power Pivot. Il contient une colonne nommée DateTime. Les valeurs s’affichent comme suit :

Si nous examinons Type de données dans l’onglet Accueil du groupe mise en forme Power Pivot, nous voyons qu’il s’agit du type de données Texte.

Nous ne pouvons pas créer de relation entre la colonne DateTime et la colonne Date dans notre table de dates, car les types de données ne correspondent pas. Si nous essayons de remplacer le type de données par Date, nous obtenons une erreur d’incompatibilité de type :

Dans ce cas, Power Pivot n’a pas pu convertir le type de données du texte en date. Nous pouvons toujours utiliser cette colonne, mais pour l’obtenir dans un type de données date réel, nous devons créer une colonne qui analyse le texte et le recrée en une valeur que Power Pivot peut créer un type de données Date.

N’oubliez pas, dans la section Utilisation du temps plus haut dans cet article ; Sauf s’il est nécessaire que votre analyse corresponde à un niveau de précision de l’heure de la journée, vous devez convertir les dates de votre table de faits en un niveau de précision jour. Dans cette optique, nous voulons que les valeurs de notre nouvelle colonne soient au niveau de précision du jour (à l’exception de l’heure). Nous pouvons à la fois convertir les valeurs de la colonne DateTime en type de données date et supprimer le niveau de précision de l’heure avec la formule suivante :

=DATE(LEFT([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 que les valeurs sont des dates et définit automatiquement le type de données sur Date.

Si nous voulons conserver le niveau de précision du temps, nous étendons simplement la formule pour inclure les heures, les minutes et les secondes.

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

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

Maintenant 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

Informations de référence sur les expressions d’analyse des données

Centre de ressources DAX