Rolling 12 mesi di media in DAXputing la media mobile di 12 mesi in DAX si presenta come un compito semplice, ma nasconde una certa complessità Questo articolo spiega come scrivere la formula migliore per evitare gli errori più comuni che utilizzano l'intelligenza tempo functions. We iniziare con il consueto modello di dati AdventureWorks , con prodotti, vendite e tavolo Calendario Il Calendario è stato contrassegnato come un tavolo di calendario, è necessario lavorare con qualsiasi funzione di tempo intelligenza e abbiamo costruito un semplice gerarchia di anno-mese-data con questo set up, è molto facile creare un prima tabella pivot che mostra le vendite oltre time. When fare analisi di tendenza, se le vendite sono soggette a stagionalità o, più in generale, se si desidera rimuovere l'effetto di picchi e cali di vendite, una tecnica comune è quella di calcolare il valore in un dato periodo , di solito 12 mesi, ed è media la media mobile su 12 mesi fornisce una superficie liscia indicatore della tendenza ed è molto utile in charts. Given una data, siamo in grado di calcolare la media mobile di 12 mesi con questa formula, che ha ancora un po ' problemi che risolveremo comportamento later. The della formula è semplice calcola il valore delle vendite dopo la creazione di un filtro sul calendario che mostra esattamente un intero anno di dati il nucleo della formula è la DATESBETWEEN, che restituisce un set comprensivo di date tra i due confini quello inferiore is. Reading dal più interno se stiamo dimostrando di dati per un mese, dicono luglio 2007 prendiamo l'ultima data visibile utilizzando ultimoData, che restituisce l'ultimo giorno nel luglio 2007 Poi usiamo NEXTDAY a prendere il 1 ° agosto 2007 e abbiamo finalmente utilizzare SAMEPERIODLASTYEAR per spostare indietro un anno, cedendo 1 Agosto 2006 il limite superiore è semplicemente ultimoData, cioè fine luglio 2007.If usiamo questa formula in una tabella pivot, il risultato sembra bene, ma abbiamo un problema per l'ultimo fatto date. In, come si può vedere in figura, il valore viene calcolato correttamente fino al 2008 poi, non vi è alcun valore nel 2009 che è corretto, non ci sono i le vendite nel 2009, ma non vi è un valore sorprendente dicembre 2010, dove la nostra formula mostra il totale complessivo, invece di un valore vuoto, come ci expect. In infatti, dicembre, ultimoData restituisce l'ultimo giorno dell'anno e NEXTDAY deve restituire dal 1 gennaio 2011, ma NEXTDAY è una funzione di intelligence in tempo e si prevede di restituire set di date esistenti Questo fatto non è molto evidente e vale la pena qualche parola funzioni di intelligence more. Time non eseguire la matematica in date Se si vuole prendere il giorno dopo una data , si può semplicemente aggiungere 1 a qualsiasi colonna della data, e il risultato sarà il giorno successivo, invece, le funzioni di intelligence in tempo spostano set di data avanti e indietro nel tempo Così, NEXTDAY prende il suo ingresso nel nostro caso una tabella di singolo-fila con il 31 dicembre 2010 e lo sposta di un giorno in più il problema è che il risultato dovrebbe essere 1 GENNAIO 2011, ma, perché la tabella calendario non contiene tale data, il risultato è BLANK. Thus, la nostra espressione calcola vendite con un limite inferiore vuoto, il che significa l'inizio del tempo, ottenendo come risultato il totale complessivo delle vendite per correggere la formula è sufficiente cambiare l'ordine di valutazione dei boundary. As inferiori si può vedere, ora NEXTDAY è chiamato dopo il turno di un anno indietro in questo modo, prendiamo il 31 dicembre 2010, si sposta al 31 dicembre 2009 e prendere il giorno successivo, che è 1 gen 2010 una data esistente nel risultato calendario table. The è ora l'atteso one. At questo punto, bisogno solo di dividere quel numero per 12 per ottenere la media mobile, ma, come si può facilmente immaginare, non possiamo sempre dividiamo per 12 infatti, all'inizio del periodo non ci sono 12 mesi di aggregare, ma un numero inferiore noi bisogno di calcolare il numero di mesi per i quali ci sono le vendite questo può essere realizzato utilizzando il filtro trasversale del tavolo calendario con la tabella di vendita dopo abbiamo applicato il nuovo contesto di 12 mesi definiamo una nuova misura che calcola il numero di mesi esistenti nel 12 mesi period. You possono vedere nella figura successiva che la misura Months12M calcola un corretto value. It vale la pena notare che la formula non funziona se si sceglie un periodo superiore a 12 mesi, in quanto il calendario MonthName ha solo 12 valori Se bisogno di periodi più lunghi, è necessario utilizzare una colonna AAAAMM essere in grado di contare più di 12. il parte interessante di questa formula che utilizza il filtraggio croce è il fatto che si calcola il numero di mesi disponibili anche quando si filtro con altri attributi If , per esempio, si seleziona il colore blu con una affettatrice, quindi vendite iniziano nel mese di luglio, non 2007 a 2005, come accade per molti altri colori uso del filtro croce sulle vendite, la formula calcola correttamente che nel luglio 2007 vi è un solo mese delle vendite disponibili per Blue. At questo punto, la media mobile è solo un DIVIDE away. When usiamo in una tabella pivot, abbiamo ancora un piccolo problema, infatti, il valore viene calcolato anche per i mesi per i quali non ci sono vendite cioè months. This future possano essere risolti usando un'istruzione IF per evitare che la formula di mostrare valori quando non ci sono le vendite non ho nulla contro IF, ma, per la prestazione-addicted in mezzo a voi, vale sempre la pena ricordare che se potrebbe essere una performance assassino, perché potrebbe costringere motore di formula DAX a calci nel in questo caso specifico, la differenza è trascurabile, ma, come regola generale, il modo migliore per rimuovere il valore quando non ci sono le vendite è di affidarsi a formule puri motore di storage come questo oneparing un grafico utilizzando il Avg12M con un altro che mostra le vendite si può facilmente apprezzare come le dolci tendenze medie contorni in una molto più pulito way. Keep Inviami articoli prossima newsletter Deselezionare per scaricare gratuitamente il file. DAX include alcune funzioni di aggregazione statistica , come media, varianza e deviazione standard Altri calcoli statistici tipici richiedono di scrivere più a lungo DAX espressioni Excel, da questo punto di vista, ha una molto più ricca Patterns language. The statistici sono un insieme di comuni calcoli statistici mediana, moda, movimento media, percentile, e quartile Vorremmo ringraziare Colin Banfield, Gerard Brueckl, e Javier Guilln, il cui blog ispirato alcuni dei seguenti pattern patterns. Basic formule Example. The in questo modello sono le soluzioni per uno specifico calculations. You statistiche possono utilizzare serie DAX funzioni per calcolare la media aritmetica dei valori di un insieme di values. AVERAGE restituisce la media di tutti i numeri in un column. AVERAGEA numerica restituisce la media di tutti i numeri in una colonna, la gestione dei valori testo e non numerici non valori di testo numerici e vuoti contare come 0.AVERAGEX calcolare la media su un'espressione valutata su una media mobile table. Moving Average. The è un calcolo per analizzare punti di dati con la creazione di una serie di medie di diversi sottoinsiemi di dati set completo è possibile utilizzare molte tecniche DAX implementare questo calcolo la tecnica più semplice utilizza AVERAGEX, iterando una tabella della granularità desiderato e calcolando per ogni iterazione l'espressione che genera il punto dati singolo da utilizzare nella media ad esempio, la seguente formula calcola la media mobile degli ultimi 7 giorni, partendo dal presupposto che si sta utilizzando una tabella data data i dati model. Using AVERAGEX, si calcola automaticamente la misura ad ogni livello di granularità Quando si utilizza una misura che può essere aggregate come SUM, poi un altro approccio basato sui cALCOLARE maggio essere più veloce è possibile trovare questo approccio alternativo nel modello completo di Moving Average. You può utilizzare le funzioni standard DAX per calcolare la varianza di un insieme di values. VAR S restituisce la varianza dei valori di una colonna che rappresentano un campione population. VAR rendimenti P la varianza dei valori di una colonna che rappresenta l'intero population. VARX S restituisce la varianza di un'espressione valutata su una tabella che rappresenta un campione population. VARX P restituisce la varianza di un'espressione valutata su una tabella che rappresenta l'intero population. Standard Deviation. You può usare funzioni standard DAX per calcolare la deviazione standard di un insieme di values. STDEV S restituisce la deviazione standard dei valori in una colonna che rappresenta un campione population. STDEV P restituisce la deviazione standard dei valori in una colonna che rappresenta l'intero population. STDEVX S restituisce la deviazione standard di un'espressione valutata su una tabella che rappresenta un campione population. STDEVX P restituisce la deviazione standard di un'espressione valutata su una tabella che rappresenta l'intero mediana population. The è il valore numerico che separa la metà superiore di una popolazione da quello inferiore metà se vi è un numero dispari di righe, la mediana è il valore centrale classificare le righe dal valore più basso al valore più alto Se c'è un numero di righe, è la media dei due valori intermedi la formula ignora valori vuoti , che non sono considerate parte della popolazione il risultato è identica alla funzione mediano Excel. Figure 1 mostra un confronto tra il risultato restituito da Excel e la corrispondente formula DAX per la mediana calculation. Figure 1 Esempio di calcolo mediano Excel e DAX. The modalità è il valore visualizzato più spesso in un insieme di dati la formula ignora valori vuoti, che non sono considerati parte della popolazione il risultato è identico al MODE e funzioni in Excel, che restituiscono solo il valore minimo quando c'è sono più modalità nel set di valori considerati la funzione di Excel restituirebbe tutti i modi, ma non si può implementare come una misura in DAX. Figure 2 paragona il risultato restituito da Excel con il corrispondente formula DAX per la modalità calculation. Figure 2 esempio di calcolo modalità in Excel e DAX. The percentile è il valore sotto al quale una data percentuale di valori in un gruppo scende la formula ignora valori vuoti, che non sono considerati parte della popolazione il calcolo in DAX richiede diversi passaggi, descritti nel completa la sezione del modello, che mostra come ottenere gli stessi risultati delle funzioni PERCENTILE Excel, and. The quartili sono tre punti che dividono un insieme di valori in quattro gruppi uguali, ogni gruppo composto da un quarto dei dati è possibile calcolare la quartili utilizzando il modello percentile, seguendo questi quartile correspondences. First basso quartile 25 percentile. Second quartile mediana 50 ° percentile. Third quartile quartile superiore 75 ° percentileplete Pattern. A alcuni calcoli statistici avere una descrizione più lunga del modello completo, perché si potrebbe avere diverse implementazioni a seconda modelli di dati e altri requirements. Moving Average. Usually si valutano la media mobile facendo riferimento al livello di granularità giorno il modello generale della seguente formula ha queste markers. numberofdays è il numero di giorni per il average. datecolumn in movimento è la data colonna della tabella data, se ne avete uno, o la colonna data della tabella contenente i valori se non vi è alcuna data table. measure separata è la misura per calcolare il movimento average. The modello più semplice utilizza la funzione AVERAGEX in DAX, che automaticamente considera solo i giorni per i quali vi è una value. As in alternativa, è possibile utilizzare il seguente modello in modelli di dati senza una tabella data e con una misura che può essere aggregate come SUM per l'intero periodo considered. The formula precedente considera un giornata senza dati corrispondenti come una misura che ha valore 0 Questo può accadere solo quando si dispone di una tabella data separato, che potrebbe contenere giorni per i quali non vi sono operazioni corrispondenti è possibile risolvere il denominatore per la media utilizzando solo il numero di giorni per che ci sono transazioni utilizzando il seguente schema, where. facttable è la tabella relativa alla tabella di data e valori contenenti calcolato dal measure. You potrebbe utilizzare le funzioni DATESBETWEEN o DATESINPERIOD invece di filtro, ma questi funzionano solo in una tabella appuntamento fisso, mentre è possibile applicare il modello sopra descritto anche alle tabelle di data non regolari e di modelli che non hanno una data table. For esempio, considerare i diversi risultati prodotti dai seguenti due measures. In figura 3, si può vedere che ci sono non effettuava vendite sul 11 settembre 2005 Tuttavia, questa data è incluso nella tabella data così, ci sono 7 giorni dall'11 settembre al 17 settembre, che hanno solo 6 giorni con data. Figure 3 Esempio di calcolo Moving Average considerazione e date ignorando con nessuna misura sales. The Moving media 7 giorni ha un numero minore tra 11 settembre e il 17 settembre, in quanto considera dell'11 settembre come un giorno con 0 vendite Se si desidera ignorare giorni con nessuna vendita, quindi utilizzare la misura media mobile 7 giorni No Zero Questa potrebbe essere l'approccio giusto quando si dispone di una tabella data completa, ma si desidera ignorare giorni senza transazioni utilizzando la media mobile 7 giorni formula, il risultato è corretto perché AVERAGEX considera automaticamente solo values. Keep non vuoto a mente che si potrebbe migliorare le prestazioni di una media mobile persistendo il valore in una colonna calcolata di una tabella con la granularità desiderato, come la data, o la data e il prodotto Tuttavia, il metodo di calcolo dinamico con una misura offre la possibilità di utilizzare un parametro per la numero di giorni di media mobile ad esempio sostituire NUMBEROFDAYS con una misura di esecuzione della mediana Parametri Tabella pattern. The corrisponde al 50 ° percentile, che si può calcolare utilizzando il modello percentile Tuttavia, il modello mediana consente di ottimizzare e semplificare il calcolo mediana utilizzando una singola misura, al posto delle diverse misure previste dal modello percentile È possibile utilizzare questo metodo quando si calcola la mediana dei valori inclusi nel valuecolumn, come mostrato below. To migliorare le prestazioni, si potrebbe voler persistere il valore di una misura in una colonna calcolata, se si vuole ottenere il mediano per i risultati di una misura nel modello di dati, tuttavia, prima di fare questa ottimizzazione, è necessario implementare il calcolo MedianX basato sul seguente modello, utilizzando questi markers. granularitytable è la tabella che definisce la granularità del calcolo, ad esempio, potrebbe essere la tabella di Data se si desidera calcolare la mediana di una misura calcolata a livello di giorno, o potrebbe essere VALORI Data annoMese se si desidera calcolare la mediana di una misura calcolata al mese level. measure è la misura di calcolare per ogni riga di granularitytable per la calculation. measuretable mediana è la tabella che contiene i dati utilizzati per misura ad esempio, se il granularitytable è una dimensione come la data, allora il measuretable sarà Internet Sales contenente la colonna Internet Sales Amount riassunta con l'esempio di Internet totale vendite measure. For, è possibile scrivere la mediana di Internet totale vendite per tutti i clienti di Adventure Works come follows. Tip il seguenti pattern. is utilizzati per rimuovere le righe da granularitytable che non hanno corrispondenti dati nella selezione corrente si tratta di un modo più veloce di utilizzare il seguente expression. However, si potrebbe sostituire l'intera espressione CALCULATETABLE con appena granularitytable se si vuole considerare valori vuoti della misura come prestazioni 0. il della formula MedianX dipende dalla numero di righe della tabella iterato e sulla complessità della misura Se le prestazioni sono male, si potrebbe persistere il risultato misura in una colonna calcolata della tabella, ma questo elimina la possibilità di applicare filtri per il calcolo mediana in fase di query. Excel ha due diverse implementazioni di calcolo percentile con tre funzioni pERCENTILE, e tutti restituire il percentile K-esimo di valori, dove K è compreso nell'intervallo 0 a 1 la differenza è che pERCENTILE e considerare K come un range inclusivo, mentre considera il K compreso tra 0 e 1 come exclusive. All di queste funzioni e implementate DAX ricevere un valore percentile come parametro, che noi chiamiamo KK valore percentile è nel range da 0 a 1.Le due DAX implementazioni di percentile bisogno di alcune misure che sono simili , ma abbastanza differente da richiedere due differenti serie di formule le misure definite in ciascun modello are. KPerc il valore percentile corrisponde K. PercPos la posizione del percentile nel set ordinato di values. ValueLow il valore inferiore del percentile position. ValueHigh il valore sopra il percentile position. Percentile il calcolo finale del percentile. You bisogno di misure ValueLow e ValueHigh nel caso in cui i PercPos contiene una parte decimale, perché poi si deve interpolare tra ValueLow e ValueHigh al fine di restituire il valore corretto percentile. la figura 4 mostra un esempio di calcoli effettuati con formule di Excel e Dax, utilizzando entrambi gli algoritmi di calcolo percentile inclusivi e exclusive. Figure 4 percentili utilizzando formule di Excel e l'equivalente DAX calculation. In sezioni seguenti, le formule percentili eseguire il calcolo sui valori memorizzato in una colonna della tabella, dati valore, mentre le formule PercentileX eseguire il calcolo sui valori restituiti da una misura calcolata in un dato esecuzione granularity. Percentile Inclusive. The percentile Inclusive è l'implementazione following. Percentile esclusive. Completano percentili Exclusive è la seguente. PercentileX Inclusive. The PercentileX implementazione Inclusive si basa sulla seguente modello, utilizzando questi markers. granularitytable è la tabella che definisce la granularità del calcolo, ad esempio, potrebbe essere il tavolo Data se si vuole calcolare il percentile di una misura alla livello di giorno, o potrebbe essere valori di data annoMese se si vuole calcolare il percentile di un provvedimento al mese level. measure è la misura di calcolare per ogni fila di granularitytable per percentile calculation. measuretable è la tabella che contiene i dati utilizzati per misura per ad esempio, se il granularitytable è una dimensione come la data, allora il measuretable sarà di vendita contenenti la colonna Importo riassunta con l'esempio Importo totale measure. For, è possibile scrivere la PercentileXInc di importo totale delle vendite per tutte le date nella tabella Data come implementazione follows. PercentileX esclusive. Completano PercentileX Exclusive si basa sulla seguente modello, utilizzando gli stessi marcatori usati nella PercentileX Inclusive. For esempio, è possibile scrivere la PercentileXExc del totale ammontare delle vendite per tutte le date nella tabella Data come segue. Inviami i modelli di prossima newsletter Deselezionare per scaricare gratuitamente il file. Published il 17 marzo 2014 by. Post navigation. Calculating una media mobile a PowerPivot. Two settimane fa ho promesso di parlare di come generare una media mobile a PowerPivot, ma poi la settimana scorsa mi sono sviato da raccontandovi un modo simpatico per visualizzare i video di YouTube nelle tue pagine di SharePoint utilizzando una parte web trovato su CodePlex che alcuni dei membri del mio team di lavoro ho trovato che fosse così facile da implementare, ho dovuto condividere con si all. However, tornando di nuovo al tema di calcolare una media mobile, la prima domanda potrebbe essere quella che è una media mobile e poi perché si vuole utilizzare uno una media mobile è semplicemente la somma di due o più valori dipendenti dal tempo in cui la somma viene poi diviso per il numero di valori utilizzati per esempio, se io parlavo i prezzi delle azioni, mi consiglia di utilizzare qualcosa come un 7 giorni di media mobile a smorzare l'effetto dei singoli picchi giornata o scende in magazzino prezzo che non sono indicativi della tendenza stock complessivo Alcuni investitori a lungo termine utilizzano anche più lungo periodo medie mobili Questo non significa che se un titolo precipita o vola che avrei sedersi fino a quando la media mobile mi dice di agire Qualsiasi buon investitore azionario dirà voi ci sono molti altri fattori sia interni che esterni di una società che potrebbe costringere la mano per vendere o comprare qualsiasi azione particolare, ma il punto è, e questa è la risposta alla seconda domanda, una media mobile smorza casualità modo che io possa più facilmente vedere il modello generale dei numeri che io sono tracking. Ok, quindi suppongo che io lavoro per Contoso e volevo sapere se le vendite sono in aumento, di caduta o generalmente piatta Se guardo vendite giornaliere, i numeri sono suscettibili di variare su e giù in nessun modello particolare mi impedisce di individuare una tendenza generale la figura seguente mostra le vendite Contoso quotidiana Contoso nel corso di un periodo di 3 mesi durante l'estate del 2008 ho scelto di mostrare i dati sotto forma di tabella per mostrare come le vendite oscillano di giorno informazioni rivelando che potrei non essere in grado di vedere come facilmente aveva ho creato una tabella con lo stesso corso values. Of, ho potuto tracciare un intero anno o più, ma per vedere i singoli giorni, avrei dovuto allargare il grafico sostanzialmente Tuttavia, anche con questa volta più piccola periodo, posso vedere che le vendite oscillano abbastanza bene ma potrei chiedere sono vendite in aumento, in diminuzione o rimanere lo stesso Se ho un buon occhio, potrei dire che il picco delle vendite verso la fine di luglio e poi ripiegare un po 'come il grafico si muove in agosto, ma che non è così evidente come il fatto che vi è una grande quantità di fluctuation. So quotidiana come posso visivamente le tendenze di visualizzazione con lo spostamento di vendite medie Ora, ai fini di questa illustrazione, ho intenzione di creare un quattro giorni di media mobile, ma onestamente, non c'è nessuno giusto numero di periodi in una media mobile in realtà, dovrei sperimentare con diversi periodi di tempo per vedere quale periodo di tempo mi permette di individuare non solo le tendenze generali, ma anche in questo caso dove ho sono la visualizzazione vendite dei negozi, a changes. I stagionale già so che se visualizzare i dati di giorno, posso usare la seguente formula per calcolare le vendite giornaliere di solo il nostro canale negozio Sì, ho potuto usare semplicemente SalesAmount e applicare una affettatrice canale da utilizzare solo le vendite dei negozi, ma lasciare che s rimanere con il example. I può quindi utilizzare questa misura calcolata per calcolare il giorno precedente s di vendita per ogni giorno creando la seguente measure. StoreSales1DayAgo CALCULATE StoreSales, DATEADD DimDate DateKey, -1, day. You potrebbe essere in grado di indovinare che la formula per calcolare le vendite di due giorni fa e tre giorni fa rispettivamente are. StoreSales2DayAgo CALCULATE StoreSales, DATEADD DimDate DateKey, -2, day. StoreSales3DayAgo cALCOLARE StoreSales, DATEADD DimDate DateKey, -3, day. With questi quattro valori calcolati per ogni giorno, posso calcolare la somma di questi valori e dividere per 4 per ottenere una media mobile a 4 giorni utilizzando il seguente calcolato value. FourDayAverage StoreSales StoreSales1DayAgo StoreSales2DayAgo StoreSales3DayAgo 4 0.Now se passo indietro alla mia pagina grafico, ho dovrebbe vedere che Excel aggiorna l'elenco campo per includere le nuove misure calcolate Se quindi aggiungere il FourDayAverage campo alla casella valori creazione di una seconda serie nel grafico, ora ho sia l'attuale vendite giornaliere e la media mobile a quattro giorni visualizzato nel stesso grafico l'unico problema è, vorrei anche voler cambiare il formato grafico per visualizzare i vendite giornaliere la mia prima serie di dati come colonne e la mia media mobile la mia seconda serie di dati come linea Quando mi fate clic destro sul grafico e selezionare Cambia tipo di grafico , posso selezionare Combo come il tipo di grafico, come mostrato nella figura seguente in questo caso, la tabella Colonna linea cluster è esattamente quello che voglio perché ho aggiunto la serie media mobile per la zona ultimi valori, diventa per default la linea e tutti altre serie di dati appaiono colonne come cluster Dal momento che ho un solo valore per ogni giorno, il grafico mostra una singola colonna per day. If ero entrato nella mia serie di dati nella zona valori nell'ordine sbagliato, si potrebbe semplicemente utilizzare questa finestra per selezionare il tipo di grafico per ogni serie quando si fa clic su OK in questa finestra di dialogo, il mio grafico ora sembra come la seguente che mostra più chiaramente la maggiore della tendenza generale e di attesa meno quotidiano fluctuation. But, c'è un modo più semplice per fare questo perché sì, ci è Ma per imparare a fare questo, si dovrà attendere fino al prossimo week. Post navigation. My Archives. Email Subscription. Topics parlo.
No comments:
Post a Comment