Totale alla data, ultimi N mesi (o media mobile ultimi N mesi…) in Power BI

Per calcolare il fatturato dell’ultimo periodo, rispetto a periodo corrente, si ricorre alla rettifica delle date del periodo considerato.

Prendiamo il caso che io voglia calcolare i fatturato LTM (Last Twelve Months) a una certa data, il cosiddetto fatturato anno mobile…

Utilizzerò per questo esempio il file “Contoso Sales”, fornito come database di test per Power BI da mamma Microsoft (qui si può scaricare il file pbix: ContosoSalesForPowerBI).

Il file viene fornito con una tabella calendario che si chiama Calendar e una tabella che ospita le vendite che si chiama Sales.

Le vendite aggregate per mese si presentano così:

Nel nostro esercizio noi costruiremo una misura per il fatturato LTM (Ultimi 12 mesi) e LQ (ultimi 3 mesi).

Per la costruzione della formula dobbiamo derogare dalle date di contesto, che sono tutte le date del mese di ogni riga; la strategia sarà prendere l’ultima data di contesto (ultimo giorno del mese se la tabella ha righe che aggregano mesi) e andare indietro di n mesi.

La nostra misura avrà questa formula:

LTM Sales = 
var CurLastDate = LASTDATE('Calendar'[DateKey].[Date])
return CALCULATE(
    SUM(Sales[SalesAmount]);
    DATESINPERIOD(
        'Calendar'[DateKey].[Date];
        CurLastDate;
        -1;
        YEAR
    )
)

La formula viene risolta in due passaggi:

• prima si calcola l’ultima data del periodo con LASTDATE;

• poi si calcolano le date del periodo di riferimento con DATESINPERIOD utilizzando CALCULATE per derogare dal contesto data in cui ci si trova.

DATESINPERIOD è una funzione che accetta 4 parametri:

• una tabella di date, usualmente il calendario;

• la data di riferimento da cui partire (estremo dell’intervallo di date a salire o scendere);

• il numero di intervalli da valutare, positivo se si vuole andare in avanti, negativo se si vuole andare indietro;

• il tipo di intervallo da considerare che può essere DAY, MONTH, QUARTER o YEAR.

Nel nostro caso il set di date va dalla data massima indietro di un anno.

Se mettiamo questa misura in tabella otterremo:

Se per ipotesi volessimo avere un fatturato medio mobile a tre mesi (media del fatturato mensile degli ultimi tre mesi) potremmo creare un formula del tipo:

Monthly Sales Average (Last 3m) = 
var CurLastDate = LASTDATE('Calendar'[DateKey].[Date])
return CALCULATE(
    SUM(Sales[SalesAmount]);
    DATESINPERIOD(
        'Calendar'[DateKey].[Date];
        CurLastDate;
        -3;
        MONTH
    )
) / 3

Per avere un risultato tipo questo:

Riferimenti DAX:

LASTDATE https://dax.guide/lastdate/
DATESINPERIOD https://dax.guide/datesinperiod/