PowerPivot SamePeriodLastYear on non contiguous date selections

Avec PowerPivot V2, vous avez un bon nombre de fonctions de manipulations de dates dont la très célèbre SamePeriodLastYear. Cette dernière permet de « retourner une table qui contient une colonne de dates décalées d’une année en arrière par rapport aux dates de la colonne dates spécifiée, dans le contexte actuel » (cf. TechNet)

Mais ce qui n’est pas indiqué, c’est qu’elle n’est utilisable que sur une plage de dates contiguës. Ce qui la rend inutile pour des analyses temporelles à périmètre constant; provoquant même un joli message d’erreur lors de la mise en place de filtres  sur des plages de dates non contiguës :

Calculation Error SAMEPERIODLASTYEAR only works with contiguous date selections

Voyons comment contourner ce problème épineux😉

Pour planter rapidement le décor, l’exemple fourni utilise la base de données AdventureWorksDW2012. Je suis parti de mon Starter Kit avec ma dimension temps universelle pour PowerPivot auquel j’ai intégré les ventes internet.

Je déclare une mesure calculée, toute bête, qui me permet d’avoir le chiffre d’affaire :

Revenue := SUM(InternetSales[SalesAmount])

Pour analyser les ventes à périmètre constant d’une année sur l’autre, je crée une première mesure calculée utilisant la fonction SAMEPERIODLASTYEAR :

Revenue LY V1 := CALCULATE(SUM(InternetSales[SalesAmount]);SAMEPERIODLASTYEAR(Calendar[Date]))

En manipulant rapidement mes données via un Pivot Table, les premiers tests ont l’air concluant sur toutes les hiérarchies calendaires. Néanmoins, dès la mise en place d’un filtre sur des dates non contigües notre message d’insulte, vu précédemment apparaît.

Pour résoudre ce problème, j’utilise la même technique vu dans un post précédent sur les Iso Last Year Value :

  • Mise en place d’une colonne calculée sur la table temps qui retourne pour chaque date, la date équivalente l’année précédente :
Same Day Last Year := DATEADD(Calendar[Date];-1;year)
  • Réécriture de la mesure calculée Revenue LY afin de filtrer le calcul sur la plage de dates (contigües ou pas) relative au context filter et correspondant au même jour l’année précédente
Revenue LY V2:= CALCULATE(
SUM(InternetSales[SalesAmount]);
FILTER(
All(Calendar);
CONTAINS(VALUES(Calendar[Same Day Last Year]);Calendar[Same Day Last Year];Calendar[Date])
)
)

Et voilà, le message d’erreur disparaît et notre analyse se fait bien à périmètre constant :

SAMEPERIODLASTYEAR on Non Contiguous Date Selections

Télécharger le fichier exemple sur SkyDrive.

Yippe ki-yay !

6 réflexions sur “PowerPivot SamePeriodLastYear on non contiguous date selections

  1. Hi, Nice workaround, however there is a small problem here, what with leap years? For example, 29th of February 2013 , same day previous year does not exist …

  2. Pingback: Power BI Desktop – dealing with time intelligence | Le Post de MCNEXT

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s