Nel mondo dell’analisi dei dati, spesso ci troviamo a gestire dataset con campi Data che richiedono visualizzazioni per specifici intervalli di tempo. Il problema più frequente è che i record che popolano questi dataset non sono sempre riferiti a tutti i giorni dell’anno bensì a specifiche date dove sono avvenuti fatti rilevanti oppure fanno riferimento alla data di estrazione dalla fonte dati. Più semplicemente il risultato è quello di un campo data non continuo che quindi risulta complicato aggregare e visualizzare (per esempio i dati delle vendite per mese oppure confrontare le vendite di un mese con mesi precedenti).
Perchè creare una Calendar Table?
Power BI, e in particolare DAX, offre una serie di “Time intelligence functions” che facilitano la creazione di KPI basati sulle date. Queste funzioni operano al meglio con una base dati che presenta campi data continui, da qui l’esigenza di integrare una calendar table nel nostro modello di dati.
Per quanto riguarda le performance del data model è importante sottolineare che di default Power BI Desktop abilita un opzione chiamata “Auto Date/Time” (che è possibile disabilitare da Options & Settings > Options> Data Load). Se questa opzione è abilitata Power BI creerà per ogni campo data che abbiamo nel dataset una gerarchia temporale (anno-mese-settimana).
Il risultato di questa operazione nel back-end, è che PBI desktop creerà per ognuna di queste colonne una nuova tabella (non visibile) con tante colonne quanti sono gli steps della gerarchia, risultando quindi in un peggioramento ed inefficienza del data model.
La soluzione consigliata è disabilitare questa opzione e utilizzare una calendar table personalizzata.
Come creare una Calendar Table in Dax
Dopo aver creato il collegamento alla tua fonte dati (noi abbiamo usato una tabella di nome Sales1 con il campo data “DateKey”) con PBI Desktop fai click su “Table view” e poi crea una nuova tabella cliccando sul apposito bottone nel banner in alto.
Qui il codice per ottenere una calendar table:
Calendar table =
ADDCOLUMNS(
CALENDAR(
DATE(YEAR(MIN(Sales1[DateKey])),01,01),
DATE(YEAR(MAX(Sales1[DateKey])),12,31)
),
“Year”,YEAR([Date]),
“Month”,FORMAT([Date],“mmm”),
“Month&Year”, FORMAT([Date],“mmm”) & ” “ & YEAR([Date]),
“Quarter”, “Q” & FORMAT([Date],“Q”),
“Month&YearOrder”, 12 * YEAR([Date]) + MONTH([Date])
)
Come potete vedere l’ultima colonna non ha a che fare con la data in sè sebbene venga costruita a partire dalla data, ma serve per ordinare in ordine corretto il campo “Month&Year” in modo che, per esempio, in un line chart i mesi vengano visualizzati in ordine corretto.
Infatti essendo il campo “Month&Year” una stringa, Power BI utilizzerà un ordinamento alfabetico per mostrarla all’interno di qualsiasi visual. Per evitare questo comportamento basterà selezionare la colonna Month&Year e utilizzare la funzione “Sort by Column” selezionando come colonna da utilizzare nell’ordinamento la colonna “Month&YearOrder”.
E’ utile capire anche il motivo per cui la colonna “Month&YearOrder” è calcolata in questo modo. Infatti moltiplicando l’anno per 12 e sommando il valore del mese possiamo creare una condizione in cui, a cavallo tra un anno ed un altro sarà possibile sottrarre o sommare il numero di mesi senza preoccuparsi della fine o l’inizio di un nuovo anno.
Esempio:
Partendo da Gennaio 2012 il valore di “Month&YearOrder” sarà 24145 e quello di Dicembre 2011 sarà 24144 e così via.
Nota: al posto di utilizzare la funzione CALENDAR e specificare la data di inizio e quella di fine, che sono sempre rispettivamente l’1 gennaio dell’anno minimo e il 31 dicembre dell’anno massimo, possiamo utilizzare anche la funzione CALENDARAUTO che a differenza della prima però prenderà come data di start e di end rispettivamente la minima e la massima data tra tutti i campi data presenti nel data set.
Come creare una calendar table in M
Cliccando su enter data nel ribbon in alto si aprirà una finestra di dialogo come quella sotto dove potremmo inserire la data di partenza della nostra calendar table.
Dopo aver inserito la MinDate possiamo procedere con l’aggiunta di una custom column che conterrà invece la nostra data di fine (Maxdate). Possiamo tranquillamente metterla in formato testo e poi cambiare il data type in Date.
Allo stesso modo costruiremo una colonna che ci permetterà di visualizzare in tabella tutte le date tra la MinDate e la MaxDate specificate in precedenza.
{Number.From([MinDate])..Number.From([MaxDate])}
Infine, dopo aver espanso la lista ed aver rimosso le colonne di MinDate e MaxDate, possiamo aggiungere colonne per le info aggiuntive di cui abbiamo bisogno tramite il linguaggio M o l’interfaccia di Power Query.
Di seguito il codice in M per creare le diverse colonne tramite la funzione custom column:
- Date.Year([Dates]) -> Anno
- Date.Month([Dates]) -> Mese
- Text.Start([MonthName],3) -> Prime tre lettere del mese
- Date.QuarterOfYear([Dates]) -> Quarto
Conclusione
Seppur esistano due alternative per creare la Calander Table, è evidente che la prima (DAX) offra una maggiore velocità di implementazione e una maggiore flessibilità nel caso in cui vadano fatti dei cambiamenti.
Ad ogni modo, avere una Calendar Table nel proprio data model ed utilizzare i campi che la compongono negli slicers del report offre molti vantaggi, basti pensare alla necessità di filtrare più campi data con un unico slicer.
Altri vantaggi sono quelli di potere utilizzare le “Time Intelligence Functions” in DAX e nel contempo ottimizzare il proprio data model per ottenere performance migliori.
Scopri le nostre soluzioni Power BI
Al tuo fianco dalla consulenza alla formazione a soluzioni di data visualization.
Visualitics Team
Questo articolo è stato scritto e redatto da uno dei nostri consulenti.
Condividi ora sui tuoi canali social o via email:
