Creazione di una tabella Date
Durante la creazione di report in Power BI, un'esigenza aziendale comune è quella di effettuare calcoli basati su data e ora. Le organizzazioni vogliono conoscere l'andamento delle loro attività nel corso dei mesi, dei trimestri, degli anni fiscali e così via. Per questo motivo è fondamentale che questi valori basati sul tempo abbiano il formato corretto. Power BI rileva automaticamente le colonne e le tabelle di date; in alcune situazioni, tuttavia, sarà necessario effettuare passaggi aggiuntivi per ottenere le date nel formato richiesto dall'organizzazione.
Si supponga, ad esempio, di stare sviluppando dei report per il team di vendita dell'organizzazione. Il database contiene tabelle per vendite, ordini, prodotti e altro ancora. Si osserva che molte di queste tabelle, incluse Sales e Order, contengono le proprie colonne di date, come mostrato dalle colonne ShipDate e OrderDate delle tabelle menzionate. Il proprio incarico consiste nello sviluppare una tabella delle vendite e degli ordini totali per anno e mese. Come creare un oggetto visivo con più tabelle, in cui ciascuna delle tabelle fa riferimento alle proprie colonne di date?
Per risolvere questo problema, è possibile creare una tabella di date comune che più tabelle potranno usare. La sezione seguente spiega come è possibile svolgere questa attività in Power BI.
Creazione di una tabella di date comune
I modi in cui è possibile creare una tabella di date comune sono:
Dati di origine
DAX
Power Query
Dati di origine
Talvolta i database di origine e i data warehouse dispongono già di proprie tabelle di date. Se l'amministratore ha progettato il database in modo accurato, queste tabelle si possono usare per svolgere le seguenti attività:
Identificazione delle festività aziendali
Separazione dell'anno di calendario dall'anno fiscale
Distinzione dei fine settimana dai giorni feriali
Le tabelle dei dati di origine sono pronte per l'uso immediato. Se si dispone di una tabella di questo tipo, inserirla nel modello semantico e non usare alcun altro metodo descritto in questa sezione. È consigliabile impiegare una tabella di date di origine perché è probabile che sia condivisa con altri strumenti in uso oltre a Power BI.
Se non si dispone di una tabella di dati di origine, è possibile usare altri metodi per creare una tabella di date comune.
DAX
È possibile usare le funzioni DAX (Data Analysis Expressions) CALENDARAUTO() o CALENDAR() per creare una tabella di date comune. La funzione CALENDAR() restituisce un intervallo contiguo di date in base a una data di inizio e di fine immesse come argomenti nella funzione. In alternativa, la funzione CALENDARAUTO() restituisce un intervallo contiguo e completo di date, determinate automaticamente in base al modello semantico. La data di inizio è la data meno recente esistente nel modello semantico, mentre la data di fine è la data più recente esistente nel modello semantico, più i dati che sono stati inseriti nel mese fiscale che è possibile scegliere di includere come argomento nella funzione CALENDARAUTO(). Ai fini di questo esempio, si usa la funzione CALENDAR() perché si desidera visualizzare solo i dati a partire dal 31 maggio 2011 (il primo giorno in cui Sales ha iniziato a monitorare questi dati) e per i successivi 10 anni.
In Power BI Desktop selezionare Nuova tabella, quindi immettere la seguente formula DAX:
Dates = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))
Ora si dispone di una colonna di date che è possibile usare. Tuttavia, questa colonna è un po' scarna. Si desidera inoltre visualizzare le colonne solo per l'anno, il numero del mese, la settimana dell'anno e il giorno della settimana. A questo scopo, selezionare Nuova colonna sulla barra multifunzione e immettere la seguente equazione DAX, che recupererà l'anno dalla tabella Date.
Year = YEAR(Dates[Date])
È possibile eseguire lo stesso processo per recuperare il numero del mese, il numero della settimana e il giorno della settimana:
MonthNum = MONTH(Dates[Date])
WeekNum = WEEKNUM(Dates[Date])
DayoftheWeek = FORMAT(Dates[Date], "DDDD")
Una volta terminato, la tabella conterrà le colonne mostrate nella figura seguente.
Si è così creata una tabella di date comune tramite DAX. Questo processo si limita ad aggiungere la nuova tabella al modello semantico; sarà comunque necessario stabilire relazioni tra la tabella di date e le tabelle Sales e Order, quindi contrassegnare la tabella come tabella di date ufficiale del modello semantico. Tuttavia, prima di completare queste attività, prendere in considerazione un altro modo per creare una tabella di date comune: l'uso di Power Query.
Power Query
Per definire una tabella di date comune, è possibile usare il linguaggio M, il linguaggio di sviluppo che serve per creare query in Power Query.
Selezionare Trasforma dati in Power BI Desktop per essere indirizzati a Power Query. Nello spazio vuoto del riquadro Query a sinistra fare clic con il pulsante destro del mouse per aprire il seguente menu a discesa, in cui selezionare Nuova query > Query vuota.
Nella vista Nuova query risultante immettere la seguente formula M per creare una tabella calendario:
= List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))
Per i dati di vendita, la data di inizio deve corrispondere alla data meno recente presente nei dati: 31 maggio 2011. Inoltre, si vogliono vedere le date per i prossimi 10 anni, comprese quelle future. Questo approccio assicura che, man mano che arrivano nuovi dati sulle vendite, non sarà necessario ricreare questa tabella. È inoltre possibile modificare la durata. In questo caso si desidera stabilire un punto dati per ogni giorno, ma è possibile anche impostare incrementi per ore, minuti e secondi. La figura seguente illustra il risultato.
Completato questo processo, è evidente che si dispone di un elenco di date anziché una tabella di date. Per correggere questo errore, andare alla scheda Trasforma sulla barra multifunzione e selezionare Converti > In tabella. Come suggerisce il nome, questa funzionalità convertirà l'elenco in una tabella. È anche possibile rinominare la colonna in DateCol.
Successivamente, occorre aggiungere colonne alla nuova tabella per visualizzare le date in termini di anno, mese, settimana e giorno, in modo da poter creare una gerarchia nell'oggetto visivo. La prima attività consiste nel cambiare il tipo di colonna selezionando l'icona accanto al nome della colonna e selezionando il tipo Data nel menu a discesa visualizzato.
Dopo aver selezionato il tipo Data, è possibile aggiungere colonne per anno, mesi, settimane e giorni. Andare ad Aggiungi colonna, selezionare il menu a discesa sotto Data, quindi scegliere Year, come mostrato nella figura seguente.
Notare che Power BI ha aggiunto una colonna di tutti gli anni estratti da DateCol.
Ripetere lo stesso processo per i mesi, le settimane e i giorni. Al termine, la tabella conterrà le colonne mostrate nella figura seguente.
Si è così creata una tabella di date comune usando Power Query.
I passaggi precedenti mostrano come inserire la tabella nel modello semantico. A questo punto è necessario contrassegnare la tabella come tabella di date ufficiale, in modo che Power BI possa riconoscerla per tutti i valori futuri e assicurare che la formattazione sia corretta.
Contrassegno della tabella come tabella di date ufficiale
La prima attività per contrassegnare la tabella come tabella di date ufficiale è individuare la nuova tabella nel riquadro Campi. Fare clic con il pulsante destro del mouse sul nome della tabella, quindi selezionare Contrassegna come tabella data, come mostrato nella figura seguente.
Contrassegnando la tabella come tabella di date si determina l'esecuzione di alcune convalide da parte di Power BI per garantire che i dati non contengano valori null, siano univoci e contengano valori di data continui per un periodo. È anche possibile scegliere colonne specifiche nella tabella da contrassegnare come data, il che può essere utile quando la tabella contiene molte colonne. Fare clic con il pulsante destro del mouse sulla tabella, selezionare Contrassegna come tabella data, quindi Impostazioni tabella data. Apparirà la seguente finestra, in cui è possibile scegliere quale colonna contrassegnare come Data.
La selezione di Contrassegna come tabella data comporta la rimozione delle gerarchie generate automaticamente dal campo Date della tabella contrassegnata come tabella data. Negli altri campi di data la gerarchia automatica sarà comunque presente finché non si stabilisce una relazione tra quel campo e la tabella di date o finché non si disattiva la funzionalità Data/ora automatica. Per aggiungere manualmente una gerarchia alla tabella di date comune, fare clic con il pulsante destro del mouse sulle colonne di anno, mese, settimana o giorno nel riquadro Campi, quindi selezionare Nuova gerarchia. Si esaminerà questo processo più avanti in questo modulo.
Creazione dell'oggetto visivo
Per creare l'oggetto visivo tra le tabelle Sales e Orders, sarà necessario stabilire una relazione tra questa nuova tabella di date comune e le tabelle Sales e Orders. Di conseguenza, sarà possibile creare oggetti visivi usando la nuova tabella di date. Per completare questa attività, andare alla scheda Modello >Gestisci relazioni, dove si possono creare relazioni tra la tabella di date comune e le tabelle Orders e Sales usando la colonna OrderDate. Lo screenshot seguente mostra un esempio di relazione di questo tipo.
Dopo aver creato le relazioni, è possibile creare l'oggetto visivo Vendite totali e Quantità ordine per Tempo con la tabella di date comune sviluppata tramite il metodo DAX o Power Query.
Per determinare le vendite totali, è necessario aggiungere tutte le vendite perché la colonna Amount nella tabella Sales considera solo i ricavi di ogni vendita, non il fatturato totale. È possibile completare questa attività usando il seguente calcolo della misura, che verrà spiegato in futuro. Il calcolo da usare per creare questa misura è il seguente:
#Total Sales = SUM(Sales[‘Amount’])
Al termine, è possibile creare una tabella tornando alla scheda Visualizzazioni e selezionando l'oggetto visivo Tabella. Poiché si desidera vedere gli ordini e le vendite totali per anno e mese, è sufficiente includere solo le colonne Year e Month della tabella di date, la colonna OrderQty e la misura #TotalSales. Quando si apprenderà delle gerarchie sarà possibile creare una gerarchia che consentirà di eseguire il drill-down passando da anni a mesi. Per questo esempio, è possibile visualizzarli affiancati. Si è così creato un oggetto visivo con una tabella di date comune.
