Time Intelligence : les bases fondamentales des calculs cumulatifs

Proposé par André Meyer-Roussilhon

14 novembre 2019

Dax

Restez informé, abonnez-vous à la newsletter (2 fois / mois)

5 + 6 =

Les briques de base de la Time Intelligence

Toute analyse historique est basée sur la modification du contexte de filtre, autrement dit sur les données prises en compte pour le calcul. Les Syntax Sugars font cela implicitement, et c’est la raison pour laquelle leur utilisation peut être complexe (on ne sait pas toujours ce qui se passe sous le capot).
C’est là qu’une bonne connaissance de la fonction CALCULATE et de la manière dont Power BI filtre les données jouent un rôle capital. Associée la FILTER, à MAX et aux opérateurs > et <, CALCULATE permet de faire à peu près tout.


Dans le cadre d’un calcul cumulatif, l’une des clés est la comparaison

Datum[Date] <= MAX(Datum[Date])


MAX([Date]) signifie ici la date courante (ce qui n’est pas nécessairement intuitif), c’est celle du contexte de filtre initial (ou naturel) : dans une table affichant les revenus quotidiens, c’est la ligne où vous vous trouvez (mais ça marcherait tout aussi bien avec les années, les trimestres, les mois etc.).

La syntaxe

FILTER (ALL(Datum) ; Datum[Date] <= MAX (Datum[Date]))

revient donc à générer une table contenant toutes les dates jusqu’à la date courante.

Et par conséquent

CALCULATE([Montant] ;
            FILTER(ALL(Datum);
                    Datum[Date] <= MAX(Datum[Date])
            )
)

permet de calculer, pour chaque ligne de la table, le montant cumulé depuis le début (première ligne de la table).

A partir de là, tout devient possible : imaginons que vous ayez lancé une campagne de promotion le 2 mai et que vous souhaitiez connaître le revenu cumulé depuis :

CALCULATE([Montant] ;
            FILTER(ALL(Datum);
              	Datum[Date] <= MAX(Datum[Date]) &&
		Datum[Date] >= DATE(2019,5,2)
            )
)


Le cumul mensuel, lui, s’écrit

CALCULATE([Montant] ;
            FILTER(ALL(Datum);
                    Datum[Date] <= MAX(Datum[Date]) &&
                    MONTH(Datum[Date]) = MONTH(MAX(Datum[Date]))
            )
)

C’est aussi simple que ça !


Nota bene : il est souvent utile, lors de la mise en place de ces formules, de créer une variable pour la date max :

VAR DDate = Max(Datum[Date])
RETURN


Dans ce cas, le CALCULATE peut s’écrire simplement :

CALCULATE([Montant] ;
            Datum[Date] <= DDate
)


Mais vous ne pouvez pas utiliser la fonction MAX directement dans le CALCULATE :

CALCULATE([Montant] ;
            Datum[Date] <= Max(Datum[Date])
)

génère une erreur « Une fonction MAX a été utilisée dans une expression TRUE/FALSE utilisée en tant qu’expression de filtre de table ».


Les Syntax Sugars


Pour ce qui concerne les cumuls, il existe un ensemble de fonctions prêtes à l’emploi. Elles sont le plus souvent simples à utiliser, et vous en trouverez des descriptions très facilement (https://docs.microsoft.com/fr-fr/dax/time-intelligence-functions-dax par exemple, ou encore https://dax.guide/).


DATESYTD et ses variantes DATESQTD et DATESMTD remplacent la partie filtre de CALCULATE, et retournent respectivement une liste de dates depuis le début de l’année / du trimestre / du mois jusqu’à la date courante :

CALCULATE(
    [Montant];
    DATESYTD(Datum[Date])
)


TOTALYTD (et TOTALQTD, TOTALMTD) simplifient encore la syntaxe (plus besoin du CALCULATE) :

TOTALYTD([Montant];
            Datum[Date])

affiche ainsi un cumul pour chaque année (idem pour les trimestres et les mois respectivement).


Notez que pour être sûr que les fonctions Time Intelligence fonctionnent correctement, il est impératif de disposer d’une table du temps.


Le cas non prévu du cumul hebdomadaire


Les Syntax Sugars sont pratiques, mais ne couvrent pas tous les cas de figure – tout ce qui sort de l’ordinaire impose de repartir de la syntaxe fondamentale à base de CALCULATE et de filtrage.

Or un type d’analyse courant n’est pas proposé par Power BI : il s’agit du cumul hebdomadaire. Voyons sa mise en place dans un exemple détaillé.


L’objectif ici est de réaliser le tableau ci-dessous, permettant de suivre le cumul hebdomadaire des montants, et de calculer le pourcentage de progression du montant cumulé par rapport au total de la semaine :


La donnée critique ici, c’est le champ Année_Semaine_ISO, dont nous avons vu la mise en place dans le billet consacré à la semaine (ICI). En effet, pour obtenir le cumul hebdomadaire, le filtre ne devra retenir que les dates inférieures ou égales à la date courante, partageant la même semaine ISO. A partir de là, le calcul est très simple :

    CALCULATE(
        [Montant];
        FILTER(ALL(Datum);
                Datum[Année_Sem_ISO] = AnSemISO_DDate &&
                Datum[Date] <= DDate
        )
    )


DDate et AnSemISO_DDate sont définies dans des variables, en utilisant les règles de calcul précisées dans le billet évoqué plus haut, consacré à la semaine. Voici donc le script en intégralité :


De même, pour la progression hebdomadaire, il suffit de diviser le cumul par le total hebdomadaire. Ce dernier est calculé à l’aide d’un filtre retenant toutes les dates partageant la même semaine que la date en cours :

Articles associés :

Créer un graphique à base zéro pour suivre l’évolution

Créer un graphique à base zéro pour suivre l’évolution

Dans un graphique à base zéro, le premier point sert de référence (le point zéro), et tous les autres sont calculés en pourcentage de variation par rapport à cette référence. Ce type de graphique est utilisé par exemple dans le domaine financier, pour suivre l’évolution du cours d’une action – mais il peut servir pour suivre l’évolution de tout type de donnée.
Le graphique est intimidant, mais sa réalisation est en fait très facile, et repose sur des formules DAX étonnamment simples.

Délai entre deux dates, week-end et jours fériés

Délai entre deux dates, week-end et jours fériés

Une table du temps qui calcule automatiquement les jours fériés ? Vous en rêviez, daxone.fr vous l’offre ! Et j’en profite pour vous donner la méthode pour calculer le nombre de jours écoulés entre deux dates, en excluant les dimanches et les jours fériés (je suis sûr que ça va vous servir)

SUMMARIZECOLUMNS : la super-fonction du DAX

SUMMARIZECOLUMNS : la super-fonction du DAX

Vous ne connaissez peut-être pas cette fonction, et c’est pourtant celle que Power BI utilise le plus fréquemment pour répondre à vos demandes : il suffit de regarder le code généré par Power BI pour afficher votre visuel pour s’en convaincre.
Avec SUMMARIZECOLUMNS , la création d’une table issue de données provenant de différentes autres tables du modèle, incluant de nouveaux indicateurs, et filtrée sur des données précises, devient un jeu d’enfant.