Una tabella calcolata molto particolare

Nella modellazione dei dati per la reportistica, uno scenario piuttosto sfidante da gestire è rappresentato dalla gestione di livelli di un’entità nel tempo, come ad esempio il saldo di conto corrente o la quantità a magazzino di un prodotto. La difficoltà sta nel fatto che non è possibile, in questi casi, aggregare nella dimensione temporale ma, allo stesso tempo, resta necessario aggregare in tutte le altre dimensioni.

Nel caso dei conti correnti, per esempio, in una certa data si possono sommare i saldi dei diversi conti ma se si considera un mese intero, o comunque un periodo con più di una data, non è possibile determinare il valore relativo sommando più valori giornalieri: va scelto di posizionarsi in una specifica data (tipicamente la massima visibile, quella di fine periodo) per sommare i saldi dei vari conti correnti in quella data, usandoli come rappresentativi dell’intero mese o altro periodo – per esempio una settimana, un trimestre o un anno.

Se, invece, si considerano le altre dimensioni di analisi – come gli intestatari dei conti o le banche -, allora certamente i saldi vanno sommati fermo restando di prendere a riferimento un’unica data.

Questo tipo di calcolo si chiama semi-additivo.
In questo primo articolo sull’argomento verrà mostrato un procedimento generale per gestire questi scenari, analizzando una soluzione che kubisco ha fornito ad un collaboratore, il dott. Luca Salvetti, che ringraziamo.

Sviluppo

Lo scenario è il seguente.
È data una tabella (Affidamenti) in cui un’azienda registra, in certe date, il saldo dei fidi in essere con varie banche.
Il saldo indicato in tabella, per una banca ad una certa data, è considerato valido fino a quando non si trovi un’ulteriore riga di aggiornamento – in una data diversa per la stessa banca.
Il saldo fidi è considerato zero, convenzionalmente, prima della prima data in cui la specifica banca viene citata. La tabella Affidamenti è visibile in figura 1.

La richiesta è di creare una nuova tabella, Affidamenti attivi, in cui siano riportate tutte le date di un certo periodo, per esempio l’intero anno 2021, e in corrispondenza di ognuna sia presente il livello di fido di ogni banca presente in Affidamenti.

Ciò significa che, in Affidamenti attivi, ogni data sarà presente tante volte quante sono in totale la banche presenti in Affidamenti.

Nel caso in esame, si tratta di cinque istituti immaginari come mostrato nella seguente tabella di sintesi (figura 2).

Un estratto della tabella finaled desiderata, Affidamenti attivi, è visibile in figura 3.

La tabella Affidamenti attivi ha 1.825 righe (365 date del 2021 per 5 istituti bancari) e in questo articolo verrà illustrato il codice DAX che la genera all’interno di un file Power BI Desktop collegato alla tabella Affidamenti, caricata da un file Excel.

In un successivo articolo verrà esaminata la costruzione di una misura per creare un report di monitoraggio.

Prima di addentrarsi sul DAX, tuttavia, è bene comprendere cosa il codice farà dal punto di vista logico – senza formule – per costruire la tabella Affidamenti attivi.

Per prima cosa, servirà una lista di date, nel caso in esame tutte le date del 2021 senza ripetizioni – un calendario, in altri termini.
Poi servirà una lista delle banche, anch’essa senza ripetizioni.
Tramite un’operazione di prodotto cartesiano, da questa due liste (due tabelle costituite da una singola colonna) si potranno ottenere la prima e terza colonna della tabella Affidamenti attivi mostrata in figura 3.

Il prodotto cartesiano tra due tabelle, infatti, genera per ogni riga della prima tabella, tante righe quante sono quelle della seconda.

Il problema da risolvere a questo punto sarà il valore di fido da inserire in ogni riga (cioè per ogni coppia data/banca) in modo da creare la seconda colonna di Affidamenti attivi, denominata Affidamento alla data.

L’algoritmo per la creazione della seconda colonna di Affidamenti attivi sarà il seguente (si ricorda che una colonna calcolata è creata iterando riga per riga la tabella su cui la si vuole creare):

• data la coppia data/banca della riga corrente in Affidamenti Attivi, si ispezionerà la tabella Affidamenti (figura 1) selezionando le sole righe relative alla banca in esame
• tra le date eventualmente presenti, si prenderà quella massima, con il vincolo che questa sia pari o antecedente alla data in esame.

Per esempio, per determinare quanto valga per Banca Popolare Fittizia il livello di fido al 5/9/21, l’algoritmo prenderà, dalla tabella Affidamenti, le sole righe in cui la banca sia  Banca Popolare Fittizia e, tra le varie date presenti – 4 in tutto come visibile in figura 4 – sceglierà la massima compatibile col fatto che non sia posteriore al 5/9/21, cioè il giorno 1/9/21 per un fido di 200.000 € che sarà riportato nella colonna Affidamento alla data della tabella Affidamenti attivi.

Adesso si può andare nel dettaglio del codice DAX di creazione della tabella Affidamenti attivi che è disponibile nell’allegato Power BI Desktop.
Il codice segue la logica appena descritta (il codice è commentato in dettaglio nel video allegato a questo articolo).

Viene creata, per prima cosa, la tabella calcolata CALENDARIO con il codice a seguire.

Nel codice della tabella CALENDARIO, la funzione CALENDARAUTO () fornisce il giusto set di date per coprire tutte quelle presenti in Affidamenti e il codice DAX genera alcune colonne ulteriori di comodo che tuttavia non sono di interesse per questo articolo, lo saranno per il successivo sulla creazione di un report. Da questa tabella, verrà selezionata, nel codice della tabella Affidamenti Attivi, soltanto la colonna di date generata da CALENDARAUTO ().

Il codice della tabella Affidamenti Attivi è mostrato a seguire. Si farà largo uso delle variabili, molto preziose per rendere il codice DAX più leggibile e performante.

Conclusioni

Il linguaggio DAX rende possibile costruire tabelle calcolate, anche molto complesse, con un codice relativamente semplice, in modo dinamico, grazie a funzioni molto potenti.

Questa caratteristica si sposa bene con lo scenario dei calcoli semiadditivi.

L’uso delle variabili è utile a rendere il codice leggibile e performante.

Autore del Post

Francesco Bergamaschi e David Bianconi

See author's posts