Moving Average Powerpivot

Trended Moving Averages Ive war schon immer fest davon überzeugt, dass bewegte Durchschnitte wahrscheinlich einen besseren Einblick in Trends innerhalb eines Unternehmens ermöglichen, als eine einfache Trendlinie, die mit einem Satz von Werten wie monatlichen Verkäufen verbunden ist (obwohl ich dazu neige, diese beiden Werte zusammen zu überprüfen). Der Grund dafür ist, dass ein Trend durch einen oder zwei Werte, die möglicherweise nicht repräsentativ für das zugrunde liegende Geschäft wie Spikes, die Saisonalität oder ein bestimmtes Ereignis zugeordnet sind, verzerrt werden kann. Wenn BillD eine Abfrage in Bezug auf dieses Konzept in seinen Kommentaren zu Profit Amp Loss (Teil 2) vergleichen und analysieren hervorgehoben. Ich dachte, dass es eine große Idee sein würde, unseren PampL Datensatz zu verdoppeln, um einige Moving Average Fähigkeit zur Verfügung zu stellen. In diesem Beitrag werde ich erklären, welche gleitenden Durchschnitte beabsichtigt sind, zu liefern und zu erklären, wie man sie mit den Verkäufelementen der Beispieldaten berechnet, die in der Profit-amp Verlust-Reihe von Pfosten verwendet werden. Ich werde dann die Flexibilität für Benutzer hinzufügen, um den Zeitrahmen auszuwählen, den die gleitende Durchschnittsberechnung berücksichtigen sollte, die Anzahl der Trendperioden, die angezeigt werden sollen, und das Endedatum des Berichts. Was ist ein gleitender Durchschnitt Die häufigste gleitende durchschnittliche Maßnahme wird im Allgemeinen als 12 Monate gleitender Durchschnitt bezeichnet. Im Falle unserer Verkaufsdaten für einen bestimmten Zeitraum würde diese Maßnahme die letzten 12 Monate des Umsatzes vor und einschließlich des zu analysierenden Monats addieren und dann durch 12 dividieren, um einen durchschnittlichen Verkaufswert für diesen Zeitraum zu zeigen. In finanzieller Hinsicht ist die Gleichung daher ganz einfach: 12 Monate bewegliche durchschnittliche Summe der Verkäufe für die letzten 12 Monate 12 Dies alles scheint sehr geradlinig, aber theres eine Menge Komplexität beteiligt, wenn wir den Moving Average Zeitrahmen (dargestellt als 12 in Das oben genannte Beispiel) in den Händen des Benutzers, geben ihnen die Macht, die Anzahl der Trendperioden anzuzeigen, die angezeigt werden sollen, und den Monat, in dem der Bericht angezeigt werden soll. Der Dataset Der Dataset, der verwendet wurde, sieht wie unten aus. Hinweis Im mit PowerPivot V1. Design-Viewer ist in V2 aber Ive hashed diese zusammen nichts clever Youll bemerken, dass FACTTran (unsere Datenmenge zu analysieren) ist mit DIMHeading1, DIMHeading2 und DIMDataType verknüpft, um einige Kategorisierung zu unserem Datensatz bieten. Ive auch im Zusammenhang mit Daten, die eine sequentielle Menge von Daten, die mehr als die Zeitspanne unseres Datensatzes. Diese Tabelle enthält einige statische Zusatzinformationen, die auf dem Datum basieren: Wieder einmal waren nicht ganz auf Robs würziger Skala festzustellen. Seien Sie versichert, dass Sie ein intensiveres DAX-Training erhalten werden, während wir weitermachen. Da diese Datumsmessungen arent erwartet werden, um dynamisch zu sein, Ive codierte sie im PowerPivot Fenster. Dies ermöglicht es ihnen, auf Datei refresh berechnet werden, aber sie müssen nicht für jede Slicer-Operation neu zu berechnen, die Performance-Overhead von unserer ultimativen dynamischen Maßnahme entfernt. Aus Gründen, die Kranke später kommen, brauche ich auch das Monatsenddatum auf meiner Faktentabelle, da ich das Monat-Enddatum nicht auf meiner Datentabelle in meinen Maßnahmen verwenden kann. Ich kann jedoch den gleichen Wert auf meine FACTTran-Tabelle mit der folgenden Maßnahme: Also, was sind diese unverknüpften MA-Tabellen Der Grund für diese Tabellen sollte deutlich werden, wie wir weitergehen. Kurz gesagt, sie werden als Parameter oder Überschriften in unserem Bericht verwendet werden. Der Grund, dass sie existieren und dass sie nicht mit dem Rest unserer Daten verbunden sind, ist einfach, weil ich nicht will, dass sie durch unsere Maßnahmen gefiltert werden. Stattdessen möchte ich, dass sie die Filterung fahren. Initial PivotTable Setup Im werde eine Reihe von Daten in monatlichen Spalten organisiert werden. Der Benutzer erhält die Aufteiler, um das Monatsenddatum (den letzten auf dem Bericht darzustellenden Zeitraum), die Anzahl der Perioden für den gleitenden Durchschnitt (dies wird letztendlich Teil unserer Divisorberechnung sein) und die Anzahl der Perioden für den Trend (dies wird sein Die Anzahl der monatlichen Spalten, die wir auf unserem Trend anzeigen werden). Wir können diese Schneidmaschinen sofort herstellen und mit dem Drehpunkt verbinden. Ich brauche offensichtlich ein Monatsenddatum als Spaltenüberschrift, aber welch ein bis zu einem gewissen Grad Ive, der dieses weg früher gegeben hat. Kurz gesagt, muss ich mein MADatesMonthEndDate Feld verwenden. Der Grund dafür ist, dass dieses Feld nicht mit unserem Datensatz verknüpft ist und daher nicht von anderen Filtern betroffen sein wird. Wenn ich ein Datumsfeld verwende, das Teil meines Datasets oder Teils einer verknüpften Tabelle ist, können die verfügbaren Werte nach den Benutzerauswahlen gefiltert werden. Ich kann um diese mit einem ALL () - Ausdruck, um mir die richtigen Werte, aber das Problem ist, dass die Spalte immer noch gefiltert und meine Ergebnisse werden alle in einer Spalte angezeigt werden. Es ist schwer zu erklären, bis Sie es sehen so gehen Sie vor und versuchen Sie es sich lohnt, die Mauer zu schlagen, um es wirklich zu verstehen Berechnen Summe der Verkäufe für die letzten X Monate Der erste Teil unserer Gleichung ist es, den Gesamtwert für den Umsatz über alle Perioden innerhalb berechnen Einen dynamischen Zeitrahmen, der durch den Benutzer ausgewählt werden soll. Dafür verwende ich eine Calculate-Funktion, die wie folgt aussieht: Ich verwende ein Basismaß namens CascadeValueAll, das in Profit amp Loss Die Art der Cascading-Zwischensumme erstellt wurde. Im dann filtern, dass Maßnahme, um meine Datenmenge auf Datensätze, die sich auf Verkäufe und einen Datentyp von Actual (dh die Beseitigung von Budget) zu begrenzen. Dies ist eine einfache Filterung einer CALCULATE-Funktion. Allerdings wird es ein bisschen mehr lecker mit dem dritten Filter, die die Datenmenge auf eine Reihe von Daten, die abhängig sind von den Benutzer-Auswahl in Slicers und unsere Spalte Datum Spalte begrenzt. Die DATESBETWEEN-Funktion hat die Syntax DATESBETWEEN (Datum, Startdatum, Enddate) und funktioniert wie folgt: Ich habe das Feld, das Filter (DatesData) benötigt. Ich habe festgestellt, dass dies am besten funktioniert, wenn dies eine verknüpfte Tabelle der sequentiellen Daten ohne Pausen ist. Wenn Sie irgendwelche Pausen haben, gibt es eine Chance, dass Sie möglicherweise keine Antwort erhalten, da die Antwort, die Sie bewerten, in der Tabelle verfügbar sein muss. Mein Startdatum ist eine DATEADD-Funktion, die das Spaltenüberschriftdatum abzüglich der Anzahl der Monate berechnet, die der Benutzer auf dem Moving Average No of Periods Slicer ausgewählt hat. Ich verwende die Funktion LASTDATE (VALUES (MADatesNextMonthStartDate)), um den Wert NextMonthStartDate aus der Tabelle MADates abzurufen, die sich auf das Datum bezieht, das in der Spaltenüberschrift dargestellt wird. Ich dann zurückspulen durch die Anzahl der Monate auf dem Slicer mit MAX (MAFunctionPeriodsMovingAverageNoPeriods) -1 ausgewählt. Das -1 wird verwendet, um in die Zeit zurückzukehren. Der Grund, warum ich NextMonthStartDate und ein Vielfaches von 1 verwenden, wird in Slicers zum Auswählen der letzten X-Perioden genauer erklärt. Mein Endedatum ist einfach das MonthEndDate, wie in der Spaltenüberschrift des Berichts angezeigt. Dies wird mit LASTDATE (VALUES (MADatesMonthEndDate) berechnet. Dies ist großartig, aber meine Maßnahme nimmt keine Berücksichtigung meiner Anzeigenperioden bis zur Auswahl und die Trend Nr der Perioden, die Ive ausgewählt. Wir müssen daher die Maßnahme nur ausführen, wenn bestimmte zu begrenzen Ich möchte nur, dass Werte angezeigt werden, wenn mein Spaltenüberschreitungsdatum ist: Kleiner als oder gleich dem ausgewählten Monatsenddatum auf meinen Anzeigenperioden Bis Slicer AND Größer als oder gleich dem ausgewählten Monatsende Datum WENIGER die ausgewählte Anzahl von Perioden auf meinem Trend No of Periods slicer. Um dies zu tun, benutze ich eine IF-Anweisung, um festzustellen, wenn meine CALCULATE-Funktion ausführen soll. Nehmen wir diese Maßnahme auf SalesMovingAverageTotalValue Die IF-Anweisung funktioniert wie folgt: Ich muss zuerst bestimmen Dass Im nur, wenn ich einen Wert für MADateMonthEndDate. Wenn ich dies nicht tun, bekomme ich, dass alte Lieblings-Fehler in meiner nachfolgenden Auswertung, dass eine Tabelle mit mehreren Werten geliefert wurde ich dann bewerten, um festzustellen, ob meine Spalte Überschrift Datum (VALUES (MADatesMonthEndDate) ist kleiner oder gleich dem Datum, das auf der Monatsendzeitspanne (LASTDATE (datesDateMonthEnd) AND (ampamp) ausgewählt wurde. Mein Spaltenüberschreitungsdatum ist größer oder gleich einem berechneten Datum, das X Perioden vor den ausgewählten Show Periods ist Bis zu, wie auf dem Slicer ausgewählt. Ich benutze eine DATEADD-Funktion für diese ähnlich wie die in meiner CALCULATE-Funktion außer wurden die Anpassung des Datums durch den Wert auf der Trend No of Periods Slicer ausgewählt. Mit diesem vorhanden, haben wir den Gesamtumsatz für den ausgewählten Zeitraum in Bezug auf die Benutzer-Auswahl. Also ist meine Tabelle nun auf die Anzahl der ausgewählten Trendperioden beschränkt und repräsentiert das ausgewählte Monatsenddatum. So, jetzt teilen wir nur durch die Gleitende Durchschnittliche Anzahl der Perioden Right eh NO Weve berechnet unsere Gesamtverkäufe für den Zeitraum in Bezug auf die Benutzer-Auswahl. Sie würden vergeben werden, für den Vorschlag, dass wir einfach durch die Anzahl der gleitenden durchschnittlichen Perioden ausgewählt. Abhängig von Ihren Daten, können Sie dies tun, aber das Problem ist, dass der Dataset möglicherweise nicht halten die ausgewählte Anzahl von Perioden, vor allem, wenn der Benutzer ein Monat Enddatum, das zurück in der Zeit wählen kann. Als Ergebnis müssen wir erarbeiten, wie können Perioden in unserer SalesMovingAverageTotalValue Maßnahme vorhanden sind. Diese Maßnahme ist im Wesentlichen die gleiche wie meine SalesMovingAverageTotal-Maßnahme. Der einzige wirkliche Unterschied ist, dass wir die unterschiedlichen Datumswerte in unserem Datensatz zählen, im Gegensatz zum Aufrufen der CascadeValueAll-Maßnahme. Ich erwähnte früher, dass es einen Grund, warum ich brauchte das Monatsende auf meinem FACTTran Tisch gehalten werden und dies ist der Grund. Wenn ich eine andere Tabelle verwenden, die das Enddatum des Monats enthält, wird diese Tabelle nicht in der Weise gefiltert, dass der Core-Dataset gefiltert wurde. Als Beispiel hat meine Dates-Tabelle eine Reihe von Daten, die meine Datenmenge Zeitrahmen und vieles mehr. Infolgedessen ergibt die Auswertung dieser Tabelle, dass die Tabelle tatsächlich Daten hat, die meinem Dataset vorangehen, und es gibt daher keine Bewertung, ob es eine Transaktion in dem Datensatz für dieses Datum stattfindet. Wie Sie sehen können, da mein Datenbestand vom 1. Juli 2009 läuft, habe ich nur 9 Perioden von Daten, die für meine Spalte 31 03 2010 zu bewerten. Wenn ich durch 12 geteilt hatte (nach meinem Moving Average No von Periods Slicer Auswahl), hätte ich eine sehr falsche Antwort bekommen. Offensichtlich ist dies etwas konstruiert, aber seine würdig zu berücksichtigen. Und jetzt die einfache Bit Ich kann verstehen, dass die letzten beiden Maßnahmen einige absorbiert haben, vor allem, wenn bestimmte Datum Felder verwendet werden. Für einige leichte Erleichterung, wird die nächste Maßnahme nicht wirklich steuern Sie Dies ist eine einfache Teilung mit ein wenig Fehlerprüfung, um jede nasties zu vermeiden. Wenn seine alle zusammengestellt werden, da alle diese Maßnahme tragbar sind, kann ich eine andere Pivot-Tabelle auf der gleichen Basis wie die oben (mit SalesMovingAverageValue bei einem Alias ​​von Moving Average) erstellen, bewegen einige Dinge herum, fügen Sie eine Maßnahme für den tatsächlichen Umsatz Wert für den Monat (Ich werde nicht in das jetzt gehen, aber seine eine einfache CALCULATE Maßnahme mit einiger Zeit Intelligenz) und ich dann rekonfigurieren, um wie folgt aussehen: Ich kann dann ein einfaches Liniendiagramm und eine Trendlinie auf meine tatsächliche Maßnahme Mit dem Diagramm bequem versteckt mein Daten-Raster, dass es fährt. Wie Sie sehen können, zeigt ein Trend auf meine Tatsächliche Maßnahme einen stetigen Rückgang. Mein Moving Average zeigt jedoch einen relativ stabilen, wenn auch nicht leicht verbesserten Trend. Saisonalität von einigen anderen Spikes sind offensichtlich daher beteiligt und die Realität ist, dass beide Maßnahmen wahrscheinlich müssen nebeneinander überprüft werden. Für diejenigen, von denen Sie lesen, die daran interessiert sind, die Arbeitsmappe dieses Beispiels sehen, Ill schauen, um dies in einem zukünftigen Beitrag Post, wenn ich diese Analyse einen Schritt weiter, um die gesamte PampL. Sorry, damit Sie warten. Ich hoffe, dies hilft Ihnen aus BillD One More Point to Note Die Adler gemusterten DAX-Profis da draußen haben wahrscheinlich bemerkt, dass meine IF-Funktionen nur eine Berechnung enthalten, um zu bewerten, wenn der logische Test eine echte Antwort erreicht. Der Grund dafür ist, dass die Funktion BLANK () übernimmt, wenn eine falsche Evaluierungsbedingung nicht bereitgestellt wird. Ich havent ausgearbeitet, wenn theres jede Leistung Auswirkungen mit dieser Methode auf große Datensätze. Seine bis zu Ihnen, was Sie gewählt haben, zu tun und wenn jemand kann mich überzeugen, warum die Kodierung der falschen Bedingung wie BLANK () ist die beste Praxis, werde ich schnell meine Gewohnheiten ändern Dieser Beitrag hat 6 Kommentare Renato Lyke sagt: Post Navigation Berechnung eines Moving Average in PowerPivot Vor zwei Wochen versprach ich, darüber zu sprechen, wie man einen gleitenden Durchschnitt in PowerPivot zu generieren, aber dann letzte Woche habe ich abgelenkt, indem ich Ihnen sagen, über eine coole Art und Weise zu zeigen, YouTube Videos auf Ihrem SharePoint-Seiten mit einem Webteil auf CodePlex gefunden, dass einige von Meine Arbeitsteammitglieder gefunden. Es war so einfach zu implementieren, ich musste es nur mit euch allen teilen. Allerdings kehrt zurück auf das Thema der Berechnung eines gleitenden Durchschnitt, könnte die erste Frage, was ist ein gleitender Durchschnitt und warum sollten Sie dann eine verwenden möchten. Ein gleitender Durchschnitt ist einfach die Summe von zwei oder mehr zeitabhängigen Werten, in denen die Summe dann durch die Anzahl der verwendeten Werte dividiert wird. Zum Beispiel, wenn ich über Aktienkurse reden, möchte ich vielleicht etwas wie ein 7-Tage gleitenden Durchschnitt verwenden, um den Effekt der einzelnen Tagesspitzen oder Tropfen in den Aktienkurs zu dämpfen, die nicht indikativ für die gesamte Aktienentwicklung sind. (Manche Langzeitinvestoren verwenden sogar längerfristig gleitende Durchschnitte.) Das bedeutet nicht, dass wenn eine Aktie stürzt oder steigt, würde ich mich zurücklehnen, bis der gleitende Durchschnitt mir sagt, dass ich handeln soll. Jeder gute Aktieninvestor wird Ihnen sagen, es gibt viele andere Faktoren sowohl intern als auch extern zu einer Firma, die Ihre Hand könnte zum Verkauf oder Kauf einer bestimmten Aktie. Aber der Punkt ist, und dies ist die Antwort auf die zweite Frage, ein gleitender Durchschnitt dämpft Zufälligkeit, so kann ich leichter sehen das allgemeine Muster der Zahlen, die ich verfolgen. Ok, so nehme ich an, für Contoso zu arbeiten und wollte wissen, ob die Verkäufe steigen, fallen oder allgemein flach sind. Wenn ich tägliche Verkäufe betrachte, sind die Zahlen wahrscheinlich, oben und unten in keinem bestimmten Muster zu schwanken, das mich vom Aufflackern eines Gesamttrends hindert. Die folgende Abbildung zeigt Contoso täglichen Contoso-Umsatz über einen Zeitraum von 3 Monaten im Sommer 2008. Ich beschloss, die Daten als Diagramm zeigen, um zu zeigen, wie Umsatz schwankt am Tag zeigen Informationen, die ich nicht in der Lage, so leicht zu sehen war Eine Tabelle mit denselben Werten erstellt. Natürlich könnte ich ein ganzes Jahr oder mehr einplanen, aber um einzelne Tage zu sehen, müsste ich das Diagramm wesentlich erweitern. Doch auch mit dieser kleineren Zeit, kann ich sehen, dass der Umsatz sehr schön schwanken. Aber ich könnte fragen, sind die Umsätze steigen, sinken oder bleiben die gleichen. Wenn ich ein gutes Auge habe, könnte ich sagen, dass Verkäufe Spitze gegen Ende Juli und dann fallen ein wenig zurück, wie das Diagramm in August geht. Aber das ist nicht so offensichtlich wie die Tatsache, dass es viel Tagesfluktuation gibt. Also, wie kann ich visuell anzeigen Trends mit Moving Average Sales. Jetzt für den Zweck dieser Illustration, Im gehend, einen viertägigen gleitenden Durchschnitt zu schaffen, aber ehrlich, gibt es keine eine richtige Anzahl von Perioden in einem gleitenden Durchschnitt. In der Tat, ich sollte mit verschiedenen Zeitperioden zu experimentieren, um zu sehen, welche Zeitspanne kann ich nicht nur die gesamte Trends, sondern auch in diesem Fall, wo ich bin Ladenverkauf, bei saisonalen Veränderungen. Ich weiß bereits, dass, wenn ich Daten von Tag anzeigen, kann ich die folgende Formel verwenden, um die täglichen Verkäufe von nur unseren Filialkanal zu berechnen. (Ja, ich könnte einfach verwenden SalesAmount und wenden Sie einen Kanal Slicer nur Store Sales verwenden, aber lässt sich mit dem Beispiel.) Ich kann dann mit diesem berechneten Maßstab, um die vorherigen Tage Umsatz für jeden Tag durch die Schaffung der folgenden Maßnahme zu berechnen. StoreSales1DayAgo: CALCULATE (StoreSales, DATEADD (DimDateDateKey, -1, Tag)) Sie können vermuten, dass die Formel für die Berechnung der Verkäufe vor zwei Tagen und drei Tagen jeweils sind: StoreSales2DayAgo: CALCULATE (StoreSales, DATEADD (DimDateDateKey, 2, Tag)) StoreSales3DayAgo: CALCULATE (StoreSales, DATEADD (DimDateDateKey, -3, Tag)) Mit diesen vier für jeden Tag berechneten Werten kann ich die Summe dieser Werte berechnen und durch 4 dividieren Den folgenden berechneten Wert: FourDayAverage: (StoreSales StoreSales1DayAgo StoreSales2DayAgo StoreSales3DayAgo) 4.0 Nun, wenn ich wieder auf meiner Kartenseite, sollte ich sehen, dass Excel die Feldliste aktualisiert, um die neuen berechneten Maßnahmen enthalten. Wenn ich dann das Feld FourDayAverage zu dem Feld Werte hinzufügen, um eine zweite Serie im Diagramm zu erstellen, habe ich jetzt sowohl den tatsächlichen täglichen Umsatz als auch den viertägigen gleitenden Durchschnitt in demselben Diagramm angezeigt. Das einzige Problem ist, ich möchte auch das Diagrammformat ändern, um den täglichen Verkauf (meine erste Datenreihe) als Spalten und meinen gleitenden Durchschnitt (meine zweite Datenreihe) als Linie anzuzeigen. Wenn ich mit der rechten Maustaste auf das Diagramm und wählen Sie Diagrammtyp ändern, kann ich Combo als Diagrammtyp wählen, wie in der folgenden Abbildung gezeigt. In diesem Fall ist das Clustered Column Line Diagramm genau das, was ich möchte. Da ich die gleitende mittlere Reihe dem Values-Bereich zuletzt hinzugefügt habe, werden sie standardmäßig die Zeile und alle anderen Datenreihen als gruppierte Spalten angezeigt. Da ich nur einen Wert für jeden Tag habe, zeigt das Diagramm eine individuelle Spalte pro Tag. Wenn ich meine Datenreihe in den Wertebereich in der falschen Reihenfolge eingetragen hatte, konnte ich einfach diesen Dialog verwenden, um den Diagrammtyp für jede Serie auszuwählen. Wenn ich in diesem Dialog auf OK klicke, sieht mein Diagramm nun wie das Folgende aus, was deutlich mehr den Gesamttrend und weniger Tagesfluktuation zeigt. Aber warten, gibt es einen einfacheren Weg, dies zu tun Warum ja gibt es. Aber um zu lernen, wie man das macht, musst du bis nächste Woche warten. Post navigation Mein Archiv E-Mail-Abonnement Themen, über die ich rede