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
        )
    )

Où 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 :