L’intelligence temporelle basée sur le calendrier modifie-t-elle la logique personnalisée ?
Introduction
Grâce à la Time Intelligence basée sur un calendrier, le besoin d’une logique Time Intelligence personnalisée a considérablement diminué.
Nous pouvons désormais créer des calendriers personnalisés pour répondre à nos besoins de calcul Time Intelligence.
Vous avez peut-être lu mon article sur la Time Intelligence avancée :
La majeure partie de la logique personnalisée n’est plus nécessaire.
Mais il existe encore des scénarios dans lesquels nous devons effectuer des calculs personnalisés, comme la moyenne mobile.
Il y a quelque temps, SQLBI a écrit un article sur le calcul de la moyenne mobile.
Cette pièce utilise les mêmes principes qui y sont décrits dans une approche légèrement différente.
Voyons comment calculer la moyenne mobile sur trois mois en utilisant les nouveaux calendriers.
Utiliser la Time Intelligence classique
Tout d’abord, nous utilisons le calendrier grégorien standard avec la table de dates classique Time Intelligence.
J’utilise une approche similaire à celle décrite dans l’article SQLBI lié dans la section Références ci-dessous.
Running Average by Month =
// 1. Get the first and last Date for the current Filter Context
VAR MaxDate = MAX( 'Date'[Date] )
// 2. Generate the Date range needed for the Moving average (three months)
VAR DateRange =
DATESINPERIOD( 'Date'[Date]
,MaxDate
,-3
,MONTH
)
// 3. Generate a table filtered by the Date Range generated at step 2
// This table contains only three rows
VAR SalesByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Date'[MonthKey]
, "#Sales", [Sum Online Sales]
)
,DateRange
)
RETURN
// 4. Calculate the Average over the three values in the table generate in step 3
AVERAGEX(SalesByMonth, [#Sales])
Lors de l’exécution de cette mesure dans DAX Studio, j’obtiens les résultats attendus :
Jusqu’ici, tout va bien.
Utiliser un calendrier standard
Ensuite, j’ai créé un calendrier nommé « Calendrier Grégorien » et j’ai modifié le code pour utiliser ce calendrier.
Pour rendre cela plus facile à comprendre, j’ai copié la table de dates dans une nouvelle table nommée « Table de dates grégoriennes ».
Le changement est lors de l’appel du DATESINPERIOD() fonction.
Au lieu d’utiliser la colonne date, j’utilise le calendrier nouvellement créé :
Running Average by Month =
// 1. Get the first and last Date for the current Filter Context
VAR MaxDate = MAX( 'Gregorian Date Table'[Date] )
// 2. Generate the Date range needed for the Moving average (three months)
VAR DateRange =
DATESINPERIOD( 'Gregorian Calendar'
,MaxDate
,-3
,MONTH
)
// 3. Generate a table filtered by the Date Range generated at step 2
// This table contains only three rows
VAR SalesByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Gregorian Date Table'[MonthKey]
, "#Sales", [Sum Online Sales]
)
,DateRange
)
RETURN
// 4. Calculate the Average over the three values in the table generate in step 3
AVERAGEX(SalesByMonth, [#Sales])
Comme prévu, les résultats sont identiques :
Les performances sont excellentes, puisque cette requête se termine en 150 millisecondes.
Utiliser un calendrier personnalisé
Mais que se passe-t-il lorsque vous utilisez un calendrier personnalisé ?
Par exemple, un calendrier avec 15 mois par an et 31 jours pour chaque mois ?
J’ai créé un tel calendrier pour mon article, qui décrit les cas d’utilisation de la Time Intelligence basée sur un calendrier (voir le lien en haut et dans la section Références).
Lorsque vous regardez le code de la mesure, vous remarquerez qu’il est différent :
Running Average by Month (Custom) =
VAR LastSelDate = MAX('Financial Calendar'[CalendarEndOfMonthDate])
VAR MaxDateID = CALCULATE(MAX('Financial Calendar'[ID_Date])
,REMOVEFILTERS('Financial Calendar')
,'Financial Calendar'[CalendarEndOfMonthDate] = LastSelDate
)
VAR MinDateID = CALCULATE(MIN('Financial Calendar'[ID_Date])
,REMOVEFILTERS('Financial Calendar')
,'Financial Calendar'[CalendarEndOfMonthDate] = EOMONTH(LastSelDate, -2)
)
VAR SalesByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Financial Calendar'[CalendarYearMonth]
, "#Sales", [Sum Online Sales]
)
,'Financial Calendar'[ID_Date] >= MinDateID
&& 'Financial Calendar'[ID_Date] <= MaxDateID
)
RETURN
AVERAGEX(SalesByMonth, [#Sales])La raison des changements est que ce tableau ne dispose pas d’une colonne de date utilisable avec le DATESINPERIOD() fonction. Pour cette raison, je dois utiliser un code personnalisé pour calculer la plage de valeurs de ID_Date.
Voici les résultats :
Comme vous pouvez le vérifier, les résultats sont corrects.
Optimisation à l’aide d’un index journalier
Mais quand j’analyse la performance, ce n’est pas terrible.
Il faut près d’une demi-seconde pour calculer les résultats.
Nous pouvons améliorer les performances en supprimant la nécessité de récupérer le minimum et le maximum ID_Date et effectuer un calcul plus efficace.
Je sais que chaque mois compte 31 jours.
Pour revenir en arrière de trois mois, je sais qu’il me faut remonter de 93 jours.
Je peux l’utiliser pour créer une version plus rapide de la mesure :
Running Average by Month (Financial) =
// Step 1: Get the last Month (ID)
VAR SelMonth = MAX('Financial Calendar'[ID_Month])
// Step 2: Generate the Date Range from the last 93 days
VAR DateRange =
TOPN(93
,CALCULATETABLE(
SUMMARIZECOLUMNS('Financial Calendar'[ID_Date])
,REMOVEFILTERS('Financial Calendar')
,'Financial Calendar'[ID_Month] <= SelMonth
)
,'Financial Calendar'[ID_Date], DESC
)
// 3. Generate a table filtered by the Date Range generated at step 2
// This table contains only three rows
VAR SalesByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Financial Calendar'[ID_Month]
, "#Sales", [Sum Online Sales]
)
,DateRange
)
RETURN
// 4. Calculate the Average over the three values in the table generate in step 3
AVERAGEX(SalesByMonth, [#Sales])
Cette fois, j’ai utilisé le TOPN() fonction pour récupérer les 93 lignes précédentes du tableau du calendrier financier et utiliser cette liste comme filtre.
Les résultats sont identiques à la version précédente :
Cette version n’a besoin que de 118 ms pour être terminée.
Mais peut-on aller encore plus loin dans l’optimisation ?
Ensuite, j’ai ajouté une nouvelle colonne au calendrier fiscal pour attribuer des rangs aux lignes. Désormais, chaque date possède un numéro unique qui est en corrélation directe avec leur ordre :
La mesure utilisant cette colonne est la suivante :
Running Average by Month (Financial) =
// Step 1: Get the last Month (ID)
VAR MaxDateRank = MAX('Financial Calendar'[ID_Date_RowRank])
// Step 2: Generate the Date Range from the last 93 days
VAR DateRange =
CALCULATETABLE(
SUMMARIZECOLUMNS('Financial Calendar'[ID_Date])
,REMOVEFILTERS('Financial Calendar')
,'Financial Calendar'[ID_Date_RowRank] <= MaxDateRank
&& 'Financial Calendar'[ID_Date_RowRank] >= MaxDateRank - 92
)
--ORDER BY 'Financial Calendar'[ID_Date] DESC
// 3. Generate a table filtered by the Date Range generated at step 2
// This table contains only three rows
VAR SalesByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Financial Calendar'[ID_Month]
, "#Sales", [Sum Online Sales]
)
,DateRange
)
RETURN
// 4. Calculate the Average over the three values in the table generate in step 3
AVERAGEX(SalesByMonth, [#Sales])
Le résultat est le même, je ne le montre plus.
Mais voici la comparaison avec les statistiques d’exécution :
Comme vous pouvez le constater, la version utilisant TOPN() est légèrement plus lent que celui utilisant la colonne RowRank.
Mais les différences sont marginales.
Plus important encore, la version utilisant la colonne RowRank nécessite plus de données pour effectuer les calculs. Voir la colonne Lignes pour plus de détails.
Cela signifie une plus grande utilisation de la RAM.
Mais avec ce petit nombre de lignes, les différences restent marginales.
C’est à vous de choisir la version que vous préférez.
Utiliser un calendrier hebdomadaire
Enfin, regardons un calcul hebdomadaire.
Cette fois, je souhaite calculer la moyenne mobile des trois dernières semaines.
Comme la Time Intelligence basée sur le calendrier permet de créer un calendrier hebdomadaire, la mesure est très similaire à la seconde :
Running Average by Week =
// 1. Get the first and last Date for the current Filter Context
VAR MaxDate = MAX( 'Gregorian Date Table'[Date] )
// 2. Generate the Date range needed for the Moving average (three months)
VAR DateRange =
DATESINPERIOD( 'Week Calendar'
,MaxDate
,-3
,WEEK
)
// 3. Generate a table filtered by the Date Range generated at step 2
// This table contains only three rows
VAR SalesByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Gregorian Date Table'[WeekKey]
, "#Sales", [Sum Online Sales]
)
,DateRange
)
RETURN
// 4. Calculate the Average over the three values in the table generate in step 3
AVERAGEX(SalesByMonth, [#Sales])
L’essentiel est que j’utilise le paramètre « WEEK » dans le DATESINPERIOD() appel.
C’est tout.
Voici le résultat de la requête :
Les performances sont excellentes, avec des temps d’exécution inférieurs à 100 ms.
Sachez que les calculs hebdomadaires ne sont possibles qu’avec la Time Intelligence basée sur le calendrier.
Conclusion
Comme vous l’avez vu, la Time Intelligence basée sur le calendrier facilite la vie avec une logique personnalisée : il suffit de transmettre le calendrier au lieu d’une colonne de date aux fonctions. Et nous pouvons calculer des intervalles hebdomadaires.
Mais l’ensemble actuel de fonctionnalités n’inclut pas d’intervalle semestriel. Lorsque nous devons calculer des résultats semestriels, nous devons soit utiliser la Time Intelligence classique, soit écrire du code personnalisé.
Mais nous avons toujours besoin d’une logique personnalisée, surtout lorsque nous n’avons pas de colonne de date dans notre tableau de calendrier. Dans de tels cas, nous ne pouvons pas utiliser les fonctions standard d’intelligence temporelle, car elles fonctionnent toujours avec des colonnes de date.
N’oubliez pas : la tâche la plus importante lorsque vous travaillez avec Time Intelligence basée sur un calendrier est de créer un tableau de calendrier cohérent et complet. D’après mon expérience, c’est la tâche la plus complexe.
En passant, j’ai trouvé quelques fonctions intéressantes sur daxlib.org concernant une moyenne mobile.
J’ai ajouté un lien vers les fonctions dans la section Références ci-dessous.
Ces fonctions suivent un modèle complètement différent, mais je voulais les inclure pour créer une image complète de ce sujet.
Références
L’article SQLBI.com mentionné sur le calcul de la moyenne courante :
https://www.sqlbi.com/articles/rolling-12-months-average-in-dax
Fonctions Time Series sur daxlib.org avec une approche différente :
https://daxlib.org/package/TimeSeries.MovingAverage
Voici mon dernier article, dans lequel j’explique l’intelligence temporelle basée sur le calendrier :
How to Implement Three Use Cases for the New Calendar-Based Time Intelligence
Comme dans mes articles précédents, j’utilise l’exemple de jeu de données Contoso. Vous pouvez télécharger gratuitement l’ensemble de données ContosoRetailDW depuis Microsoft. ici.
Les données Contoso peuvent être utilisées librement sous la licence MIT, comme décrit dans ce document. J’ai modifié l’ensemble de données pour déplacer les données vers des dates contemporaines.
