DAX e Time Intelligence, Period To Date (Cumulative Total) con esempi

I Pattern Period To Date (Cumulative Total)

I pattern “Period To Date” (noti anche come “Cumulative Total”) servono a calcolare valori aggregati dall’inizio di un periodo fino alla data corrente. Sono molto utilizzati per monitorare l’andamento di un KPI nel tempo e rispondere a domande del tipo: “a quanto siamo arrivati finora, rispetto al periodo in corso?”. In Power BI, i casi più comuni sono:

Con il termine “data corrente” si intende, nella maggior parte dei casi, la data (o l’intervallo di date) definita dal Filter Context: ad esempio tramite uno slicer, un filtro di pagina oppure il livello temporale utilizzato nel visual (anno, mese, giorno). In alternativa, la data di riferimento può essere definita esplicitamente nell’espressione DAX, ad esempio passando una colonna di date o combinando queste funzioni con CALCULATE per controllare in modo più fine il contesto temporale del calcolo.

La Funzione TOTALYTD

La funzione TOTALYTD calcola l’espressione specificata sull’intervallo temporale che inizia dal primo giorno dell’anno corrente, e termina con l’ultima data visibile nella colonna di date indicata, dopo aver applicato i filtri presenti nel Filter Context. In pratica, TOTALYTD restituisce un valore cumulativo Year-To-Date (YTD): somma progressivamente i valori dal 1° gennaio dell’anno corrente fino alla data corrente determinata dal contesto di filtro. La sua sintassi è la seguente:

TOTALYTD(Expression, Dates, Filter, YearEndDate)

Supponiamo di avere la seguente Misura di base:

// Measure
Total Sales =
    SUMX (
        Winesales,
        Winesales[CASES SOLD] * RELATED(Wines[PRICE PER CASE])
    )

La corrispondente Misura “Year-To-Date” che calcola il cumulato delle vendite dall’inizio dell’anno diventa:

// Measure
Total Sales YTD =
    TOTALYTD(
        [Total Sales],      // expression
        DateTable[DATEKEY]  // dates column (must be filtered by Filter Ctx)
    )

È importante notare che la colonna delle date (in questo esempio: DateTable[DATEKEY]) deve appartenere a una Date Table correttamente configurata, e deve essere filtrata dal Filter Context, ad esempio tramite un Visual, uno Slicer o una riga di una Matrice o Tabella. Se la data corrente è:

• il 15/3/2024, la Misura somma tutte le vendite dal 1/1/2024 al 15/3/2024.
• il 20/10/2024, la Misura somma tutte le vendite dal 1/1/2024 al 20/10/2024.

In entrambi i casi, il risultato dipende esclusivamente dall’ultima data visibile nel Filter Context. Come esempio, si veda il tab “Period To Date #1”:

La funzione TOTALYTD è essenzialmente una scorciatoia sintattica. Internamente, equivale a una CALCULATE che utilizza la funzione DATESYTD, la quale restituisce un set di date che va dal primo giorno dell’anno corrente, fino all’ultima data visibile nel Filter Context. Ad esempio:

// this Measure...
Total Sales YTD =
    TOTALYTD(
        [Total Sales],          // expression
        DateTable[DATEKEY]      // dates column
    )

// is equivalent to this Measure
Total Sales YTD #2 =
    CALCULATE(
        [Total Sales],          // expression
        DATESYTD(               // DATESYTD
            DateTable[DATEKEY]  //   dates column
        )
    )

Entrambe le Misure producono lo stesso risultato, ma TOTALYTD rende il codice più compatto, leggibile e immediatamente riconoscibile come calcolo di Time Intelligence di tipo Year-To-Date.

Altri Semplici Esempi

Di seguito ossiamo vedere altri semplici esempi dell’uso della funzione TOTALYTD:

Quantità venduta YTD

Stesso pattern di Total Sales YTD, ma applicato a una misura di quantità:

Total Quantity =
   SUM(Winesales[CASES SOLD])

Total Quantity YTD =
   TOTALYTD (
       [Total Quantity],
       DateTable[DATEKEY]
   )

Vendite YTD solo per prodotti attivi

Classico esempio con filtro aggiuntivo come terzo parametro della funzione TOTALYTD (che va in AND logico con il Filter Context esistente):

Total Sales YTD (Active Products) =
    TOTALYTD (
        [Total Sales],                 // Expression
        DateTable[DATEKEY],            // Dates
        Products[IS ACTIVE] = TRUE ()  // Filter (optional)
    )

Vendite YTD con anno fiscale non solare

In questo esempio settiamo il parametro YearEndDate, per usare un calendario fiscale non allineati all’anno solare, che termina il 30 giugno:

Total Sales YTD (Fiscal) =
    TOTALYTD (
        [Total Sales],    // Expression
        DateTable[DATE],  // Dates
        ,                 // Filter (optional)
        "06/30"           // YearEndDate (optional)
    )

In questo caso, l’anno fiscale va dal 1 luglio al 30 giugno, e il cumulato riparte dal 1 luglio.

La Funzione TOTALQTD

La funzione TOTALQTD calcola l’espressione specificata nell’intervallo temporale che inizia dal primo giorno del trimestre corrente, e termina con l’ultima data visibile nella colonna di date indicata, dopo aver applicato eventuali filtri aggiuntivi. In pratica, TOTALQTD consente di calcolare un valore Quarter To Date (QTD), ovvero una somma (o qualsiasi altra aggregazione) progressiva dal primo giorno del trimestre fino alla data corrente nel Filter Context. La sintassi della funzione è la seguente:

TOTALQTD(Expression, Dates, Filter)

Supponiamo di avere la Misura “Total Sales” (utilizzata anche negli esempi di TOTALYTD). La versione “Quarter To Date” diventa quindi:

// Measure
Total Sales QTD =
    TOTALQTD(
        [Total Sales],      // expression
        DateTable[DATEKEY]  // dates column
    )

Il comportamento della Misura dipende dalla data corrente nel Filter Context. Se la data corrente è:

• il 15/3/2024, la Misura somma tutte le vendite dal 1/1/2024 (inizio del primo trimestre) al 15/3/2024.
• il 20/11/2024, la Misura somma tutte le vendite dal 1/10/2024 (inizio del quarto trimestre) al 20/11/2024.

Come esempio, si veda il tab “Period To Date #2”:

Analogamente a TOTALYTD, anche TOTALQTD è una funzione di comodo (wrapper). Internamente, essa equivale a una chiamata a CALCULATE con la funzione DATESQTD, che restituisce l’insieme delle date che va dall’inizio del trimestre corrente fino all’ultima data visibile nel Filter Context. Ad esempio, le due Misure seguenti sono logicamente equivalenti:

// this Measure...
Total Sales QTD =
    TOTALQTD(
        [Total Sales],          // expression
        DateTable[DATEKEY]      // dates column
    )

// is equivalent to this Measure
Total Sales QTD #2 =
    CALCULATE(
        [Total Sales],          // expression
        DATESQTD(               // DATESQTD
            DateTable[DATEKEY]  //   dates column
        )
    )

Altri Semplici Esempi

Di seguito ossiamo vedere altri semplici esempi dell’uso della funzione TOTALQTD:

Quantità venduta QTD

Stesso pattern di Total Sales YTD, ma applicato a una misura di quantità:

Total Cases =
   SUM(Winesales[CASES SOLD])

Total Cases QTD  =
   TOTALQTD(
       [Total Cases],
       DateTable[DATEKEY]
   )

Vendite QTD con filtro aggiuntivo

Se volessimo calcolare le vendite “Quarter To Date” limitate ad un paese specifico:

// Measure
Total Sales QTD France =
    TOTALQTD(
        [Total Sales],                  // Expression
        DateTable[DATEKEY],             // Dates
        Wines[WINE COUNTRY] = "France"  // Filter (optional)
    )

Vendite QTD dell’anno corrente

Per forzare il calcolo del QTD solo sull’anno corrente, indipendentemente dai filtri applicati nel report:

// Measure
Total Sales QTD CY =
    TOTALQTD(
        [Total Sales],                   // Expression
        DateTable[DATEKEY],              // Dates
        DateTable[YEAR] = YEAR(TODAY())  // Filter (optional)
    )

La Funzione TOTALMTD

La funzione TOTALMTD segue la stessa logica di TOTALQTD, ma applicata a un intervallo mensile. Essa calcola l’espressione specificata nel periodo che inizia dal primo giorno del mese corrente e termina con l’ultima data visibile nella colonna di date indicata, dopo aver applicato i filtri presenti nel Filter Context. TOTALMTD consente quindi di calcolare un valore Month To Date (MTD), ossia una somma (o qualsiasi altra aggregazione) progressiva dal primo giorno del mese fino alla data corrente selezionata nel report. In pratica, la funzione è particolarmente utile per:

• monitorare l’andamento mensile delle vendite o dei costi;
• costruire KPI di avanzamento rispetto al mese in corso;
• confrontare facilmente il progresso del mese corrente con periodi precedenti.

La sintassi della funzione è la seguente:

TOTALMTD(Expression, Dates, Filter)

Come per le altre funzioni “Period To Date”, il corretto funzionamento di TOTALMTD richiede la presenza di una Date Table completa e correttamente collegata al modello. Di seguito alcuni semplici esempi:

// Measure
Total Sales MTD =
    TOTALMTD(
        [Total Sales],
        DateTable[DATEKEY]
    )

// Measure
Total Quantity MTD =
    TOTALMTD(
        SUM(Winesales[CASES SOLD]),
        DateTable[DATEKEY]
    )

// Measure
Total Cost MTD =
    TOTALMTD(
        [Total Cost],
        DateTable[DATEKEY]
    )

Confronto tra Funzioni