Un puzzle delle misure YTD in DAX

Quando si fa consulenza in ambito BI, è frequente trovare una tabella Calendario già presente nel data warehouse o, in generale, nel database a cui ci si collega tramite Visual Studio, Power BI Desktop o Power Pivot per Excel.

Fino a quando questo calendario ha le caratteristiche essenziali per usare la Time Intelligence in DAX (essere completo di tutti i giorni ed avere una chiave primaria di tipo data), trovarne uno pronto all’uso è una gradita sorpresa.

Tuttavia, una delle tipiche caratteristiche di questi calendari può causare un grattacapo con i calcoli YTD che, in questo articolo, verrà mostrato e risolto. Qui faccio riferimento al mio caro amico e cliente Marcello Righi, Chief Marketing Officer di MOMA Group; è proprio durante una consulenza alla sua azienda che è emerso questo singolare problema.

Il modello in uso è, come sempre, molto semplice ed è mostrato in figura 1.

In figura 1, Calendar è la tabella calendario trovata nel database. Successivamente, verrà aggiunto un calendario creato in DAX, che sarà chiamato My Calendar, e che servirà da debug.

Sviluppo

Si supponga di volere effettuare due semplici calcoli: l’ammontare delle vendite e delle vendite cumulate annuali, usando la tabella Calendar. In figura 2 è visibile il risultato.

Come si nota, manca il valore della misura Sales Amount YTD Corp Cal al totale. Il codice della misura è a seguire:

Sales Amount YTD Corp Cal =

Ed ecco quello di Sales Amount:

Sales Amount =

Che dire dello strano risultato in figura 2? La misura non mostra difetti. Il primo indiziato è la tabella Calendar. Eppure, essa ha entrambe le caratteristiche listate sopra (completezza e dotazione di una chiave primaria di tipo data).

A questo punto, può essere utile creare un calendario in DAX per capire se veramente sia un problema di calendario. La tabella My Calendar ha il seguente codice, piuttosto classico:

My Calendar =

Ecco l’effetto dell’uso di My Calendar, una volta creata la relazione con la tabella Facts:

In figura 3, il totale della misura Sales Amount YTD My Cal è visibile. Il relativo codice è identico a quello di Sales Amount YTD Corp Cal, a parte l’uso di un differente calendario:

Sales Amount YTD My Cal =

Dunque, non vi è dubbio: il problema è il calendario.

Che differenza c’è tra i due calendari? Guardando le visualizzazioni non si nota, ma il calendario disponibile nel database (Calendar) si estende oltre gli anni dei fatti (arriva al 31/12/2025 mentre i fatti si fermano ad aprile 2023). Questo è un caso tipico, visto che il dipartimento IT gradisce, comprensibilmente, fare le cose per non doverci tornare sopra per un po’, oltre al fatto che il calendario potrebbe essere usato da altri report in cui le date sono, magari, previsionali o di budget.

Questo, però non dovrebbe essere un problema, e infatti non lo è per le misure che non coinvolgono calcoli YTD. Nel caso YTD, invece, il problema esiste: DATESYTD, in presenza di più anni nel filter context, calcola il valore della misura primo argomento di CALCULATE in un intervallo di date che va dalla prima data alla massima data visibile, ma entrambe riferite al massimo anno nel contesto. Se la misura risulta BLANK in quell’anno, il totale sarà BLANK. Dunque, bisogna eliminare dal calendario del database gli anni successivi al massimo anno dei fatti. Nel caso del calendario scritto in DAX, viene naturale evitare che questo accada. Diverso è il caso di un calendario creato dal dipartimento IT.

Eliminare dal calendario del database gli anni successivi al massimo anno dei fatti in maniera manuale, tuttavia, è semplice ma da fare ogni anno. Ecco come fare in modo automatico tramite Power Query: basterà aggiungere una colonna personalizzata che calcoli l’anno della data odierna – codice M: Date.Year(DateTime.LocalNow()) – e poi un’ulteriore colonna, questa volta condizionale, che abbia come valore, per esempio, Y se l’anno del calendario è minore o uguale all’anno della data odierna e N altrimenti. Successivamente basterà filtrare quella colonna per il valore Y.

Fatto questo, la misura Sales Amount YTD Corp Cal mostra correttamente il valore al totale anche usando la tabella Calendar:

Per evitare che le misure YTD mostrino valori oltre la data odierna – o la massima data di vendita con una piccola modifica – cosa che sarebbe corretta come calcolo ma scomoda per gli utenti, le due misure possono essere così modificate (rif. questo ottimo articolo di SQLBI):

Sales Amount YTD Corp Cal = Sales Amount YTD My Cal =

Infine, come ottenere al totale, riguardo le misure YTD, il valore totale delle vendite di tutti gli anni? Ecco due esempi, che usano entrambi i calendari:

Sales Amount YTD Corp Cal With Modified Total = Sales Amount YTD My Cal With Modified Total =

Conclusioni

I calendari disponibili nei e nei database corporate in generale sono comodi e utili, a volte indispensabili nel caso di specifiche necessità, complesse sa replicare in DAX. Tuttavia, attenzione ai calcoli YTD: questo necessitano di un calendario che non vada oltre il 31/12 dell’anno della massima data dei fatti. Con un piccolo sforzo in Power Query è possibile automatizzare la selezione delle righe del calendario corporate in modo da essere in linea con questa necessità. La cosa è meno problematica nei calendari scritti in DAX che vengono naturalmente estesi non oltre l’anno della massima data dei fatti.

Autore del Post