Verstehen und Erstellen von Datumstabellen in Power Pivot in Excel

Erstellen einer Datumstabelle in Excel

Sie können eine Datumstabelle in Excel erstellen und diese dann in eine neue Tabelle im Datenmodell kopieren. Dies ist wirklich ganz einfach und bietet Ihnen viel Flexibilität.

Wenn Sie eine Datumstabelle in Excel erstellen, beginnen Sie mit einer einzelnen Spalte mit einem zusammenhängenden Datumsbereich. Sie können dann zusätzliche Spalten wie Jahr, Quartal, Monat, Geschäftsjahr, Zeitraum usw. im Excel-Arbeitsblatt erstellen, indem Sie Excel-Formeln verwenden, oder sie können nach dem Kopieren der Tabelle in das Datenmodell als berechnete Spalten erstellt werden. Das Erstellen zusätzlicher Datumsspalten in Power Pivot wird weiter unten in diesem Artikel im Abschnitt Hinzufügen neuer Datumsspalten zur Datumstabelle beschrieben.

Hinzufügen neuer Datumsspalten zur Datumstabelle

Eine Datumstabelle mit einer einzelnen Datumsspalte, die eine Zeile für jeden Tag für jedes Jahr enthält, ist wichtig, um alle Datumsangaben in einem Datumsbereich zu definieren. Es ist auch erforderlich, um eine Beziehung zwischen der Faktentabelle und der Datumstabelle zu erstellen. Diese einzelne Datumsspalte mit einer Zeile für jeden Tag ist jedoch bei der Analyse nach Datumsangaben in einem PivotTable- oder Power View-Bericht nicht hilfreich. Die Datumstabelle soll Spalten enthalten, mit denen Sie Ihre Daten für einen Bereich oder eine Gruppe von Datumsangaben aggregieren können. Sie können z. B. Umsatzbeträge nach Monat oder Quartal summieren oder ein Measure erstellen, das das Wachstum im Jahresverlauf berechnet. In jedem dieser Fälle benötigt Ihre Datumstabelle Jahres-, Monats- oder Quartalsspalten, mit denen Sie Ihre Daten für diesen Zeitraum aggregieren können.

Wenn Sie Ihre Datumstabelle aus einer relationalen Datenquelle importiert haben, enthält sie möglicherweise bereits die verschiedenen Typen von Datumsspalten, die Sie möchten. In einigen Fällen können Sie einige dieser Spalten ändern oder zusätzliche Datumsspalten erstellen. Dies gilt insbesondere, wenn Sie eine eigene Datumstabelle in Excel erstellen und in das Datenmodell kopieren. Glücklicherweise ist das Erstellen neuer Datumsspalten in Power Pivot mit Datums- und Uhrzeitfunktionen in DAX ganz einfach.

DAX-Datums- und Uhrzeitfunktionen

Wenn Sie schon einmal mit Datums- und Uhrzeitfunktionen in Excel-Formeln gearbeitet haben, sind Sie wahrscheinlich mit den Datums- und Uhrzeitfunktionen vertraut. Obwohl diese Funktionen ihren Entsprechungen in Excel ähneln, gibt es einige wichtige Unterschiede:

• DAX-Funktionen für Datum und Uhrzeit verwenden einen datetime-Datentyp.

• Sie können Werte aus einer Spalte als Argument verwenden.

• Sie können verwendet werden, um Datumswerte zurückzugeben und/oder zu bearbeiten.

Diese Funktionen werden häufig beim Erstellen benutzerdefinierter Datumsspalten in einer Datumstabelle verwendet, daher sind sie wichtig zu verstehen. Wir verwenden eine Reihe dieser Funktionen, um Spalten für Year, Quarter, FiscalMonth usw. zu erstellen.

DAX umfasst die folgenden Datums- und Uhrzeitfunktionen:

• DATUM

• DATEVALUE

• NÄCHSTER TAG

• EDATE

• EOMONTH

• STUNDE

• MINUTE

• MONAT

• JETZT

• SEKUNDE

• ZEIT

• ZEITWERT

• HEUTE

• WOCHENTAG

• WEEKNUM

• JAHR

• YEARFRAC

Es gibt auch viele andere DAX-Funktionen, die Sie in Ihren Formeln verwenden können. Viele der hier beschriebenen Formeln verwenden beispielsweise mathematische und trigonometrische Funktionen wie MOD und TRUNC, logische Funktionen wie IF und Textfunktionen wie FORMAT Weitere Informationen zu anderen DAX-Funktionen finden Sie im Abschnitt Zusätzliche Ressourcen weiter unten in diesem Artikel.

Formelbeispiele für ein Kalenderjahr

In den folgenden Beispielen werden Formeln beschrieben, die zum Erstellen zusätzlicher Spalten in einer Datumstabelle namens Calendar verwendet werden. Eine Spalte mit dem Namen Date ist bereits vorhanden und enthält einen zusammenhängenden Datumsbereich vom 1.1.2010 bis zum 31.12.2016.

Month Name

=FORMAT([Datum],"mmmm")

In dieser Formel verwenden wir zum Abrufen des Monatsnamens die FORMAT-Funktion , um einen numerischen Wert aus der Spalte Date in Text zu konvertieren. Wir geben die Spalte Date als erstes Argument und dann das Format an. wir möchten, dass der Monatsname alle Zeichen anzeigt, daher verwenden wir "mmmm". Unser Ergebnis sieht wie folgt aus:

Wenn wir den auf drei Buchstaben abgekürzten Monatsnamen zurückgeben möchten, verwenden wir "mmm" im Format-Argument.

Wochentag

=FORMAT([Datum],"ddd")

In dieser Formel verwenden wir die FORMAT-Funktion, um den Tagnamen abzurufen. Da wir nur einen abgekürzten Tagnamen benötigen, geben wir "ddd" im Format-Argument an.

Beispiel-PivotTable

Sobald Sie Felder für Datumsangaben wie Jahr, Quartal, Monat usw. haben, können Sie diese in einer PivotTable oder einem Bericht verwenden. Die folgende Abbildung zeigt z. B. das Feld SalesAmount aus der Faktentabelle Sales in VALUES und Year and Quarter aus der Dimensionstabelle Calendar in ROWS. SalesAmount wird für den Jahres- und Quartalskontext aggregiert.

Formelbeispiele für ein Geschäftsjahr

Feiertage oder besondere Termine

Möglicherweise möchten Sie eine Datumsspalte einschließen, die angibt, dass bestimmte Datumsangaben Feiertage oder ein anderes besonderes Datum sind. Sie können z. B. die Gesamtsummen der Umsätze für den Neujahrstag addieren, indem Sie einer PivotTable ein Feiertagsfeld als Slicer oder Filter hinzufügen. In anderen Fällen können Sie diese Datumsangaben aus anderen Datumsspalten oder in einem Measure ausschließen.

Feiertage oder besondere Tage sind ganz einfach. Sie können eine Tabelle in Excel erstellen, die die Datumsangaben enthält, die Sie einschließen möchten. Anschließend können Sie das Datenmodell kopieren oder verwenden, um es dem Datenmodell als verknüpfte Tabelle hinzuzufügen. In den meisten Fällen ist es nicht erforderlich, eine Beziehung zwischen der Tabelle und der Calendar-Tabelle zu erstellen. Alle Formeln, die darauf verweisen, können die LOOKUPVALUE-Funktion verwenden, um Werte zurückzugeben.

Im Folgenden finden Sie ein Beispiel für eine in Excel erstellte Tabelle, die Feiertage enthält, die der Datumstabelle hinzugefügt werden sollen:

In der Datumstabelle erstellen wir eine Spalte namens Holiday und verwenden eine Formel wie die folgende:

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

Sehen wir uns diese Formel genauer an.

Wir verwenden die FUNKTION LOOKUPVALUE, um Werte aus der Spalte Feiertag in der Tabelle Holidays abzurufen. Im ersten Argument geben wir die Spalte an, in der sich unser Ergebniswert befindet. Wir geben die Spalte Feiertag in der Tabelle Holidays an, da dies der Wert ist, den wir zurückgeben möchten.

=LOOKUPVALUE(Holidays[Holiday];Holidays[date],Calendar[date])

Anschließend geben wir das zweite Argument an, die Suchspalte mit den Datumsangaben, nach denen gesucht werden soll. Wir geben die Spalte Date in der Tabelle Holidays wie folgt an:

=LOOKUPVALUE(Holidays[Holiday],Holidays[date];Calendar[date])

Schließlich geben wir die Spalte in unserer Kalendertabelle an, die die Datumsangaben enthält, nach denen wir in der Tabelle Feiertag suchen möchten. Dies ist natürlich die Spalte Date in der Kalendertabelle .

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

Die Spalte Feiertag gibt den Feiertagsnamen für jede Zeile zurück, die einen Datumswert aufweist, der mit einem Datum in der Tabelle Holidays übereinstimmt.

Benutzerdefinierter Kalender – dreizehn Vier-Wochen-Zeiträume

Einige Organisationen, wie Einzelhandel oder Gastronomie, berichten häufig über verschiedene Zeiträume, z. B. dreizehn Vier-Wochen-Zeiträume. Bei einem dreizehn Vier-Wochen-Kalender beträgt jeder Zeitraum 28 Tage; Daher enthält jeder Zeitraum vier Montage, vier Dienstage, vier Mittwoche usw. Jeder Zeitraum enthält die gleiche Anzahl von Tagen, und normalerweise fallen Feiertage jedes Jahr innerhalb desselben Zeitraums. Sie können einen Zeitraum an einem beliebigen Tag der Woche beginnen. Genau wie bei Datumsangaben in einem Kalender oder Geschäftsjahr können Sie DAX verwenden, um zusätzliche Spalten mit benutzerdefinierten Datumsangaben zu erstellen.

In den folgenden Beispielen beginnt der erste vollständige Zeitraum am ersten Sonntag des Geschäftsjahres. In diesem Fall beginnt das Geschäftsjahr am 7.1.

Period

Der Zeitraum in diesem benutzerdefinierten Kalender umfasst 28 Tage und beginnt immer an einem Sonntag. Diese Spalte gibt die Nummer des Zeitraums zurück, der mit dem ersten Sonntag im ersten Geschäftsjahr beginnt.

=INT(([Woche]+3)/4)

Sehen wir uns diese Formel genauer an.

Zunächst erstellen wir eine Formel, die einen Wert aus der Spalte Week als ganze Zahl zurückgibt, wie hier:

=INT([Woche])

Fügen Sie dann diesem Wert wie folgt 3 hinzu:

=INT([Woche]+3)

Dividieren Sie dann das Ergebnis wie folgt durch 4:

=INT(([Woche]+3)/4)

Das Ergebnis sieht wie folgt aus:

Zeitraum Geschäftsjahr

Dieser Wert gibt das Geschäftsjahr für einen Zeitraum zurück.

=INT(([Period]+12)/13)+2008

Sehen wir uns diese Formel genauer an.

Zunächst erstellen wir eine Formel, die einen Wert aus Period zurückgibt und 12 hinzufügt:

= ([Zeitraum]+12)

Wir dividieren das Ergebnis durch 13, da es dreizehn 28-Tage-Zeiträume im Geschäftsjahr gibt:

=(([Zeitraum]+12)/13)

Wir fügen 2010 hinzu, da dies das erste Jahr in der Tabelle ist:

=(([Zeitraum]+12)/13)+2010

Schließlich verwenden wir die INT-Funktion, um jeden Bruchteil des Ergebnisses zu entfernen und eine ganze Zahl zurückzugeben, wenn sie durch 13 dividiert wird, wie folgt:

=INT(([Period]+12)/13)+2010

Das Ergebnis sieht wie folgt aus:

Zeitraum in FiscalYear

Dieser Wert gibt die Periodennummer 1 bis 13 zurück, beginnend mit dem ersten vollständigen Zeitraum (beginnend am Sonntag) in jedem Geschäftsjahr.

=WENN(MOD([Zeitraum],13); MOD([Zeitraum],13);13)

Diese Formel ist etwas komplexer, daher werden wir sie zuerst in einer Sprache beschreiben, die wir besser verstehen. Diese Formel gibt an, den Wert von [Zeitraum] durch 13 zu dividieren, um eine Periodenzahl (1-13) im Jahr zu erhalten. Wenn diese Zahl 0 ist, geben Sie 13 zurück.

Zunächst erstellen wir eine Formel, die den Rest des Werts aus Period um 13 zurückgibt. Wir können die MOD (Mathematische und Trigonometrische Funktionen) wie folgt verwenden:

=MOD([Zeitraum],13)

Dadurch erhalten wir größtenteils das gewünschte Ergebnis, außer wenn der Wert für Period 0 ist, da diese Datumsangaben nicht innerhalb des ersten Geschäftsjahres liegen, wie in den ersten fünf Tagen unserer Kalenderdatumstabelle. Wir können dies mit einer WENN-Funktion erledigen. Wenn unser Ergebnis 0 ist, geben wir 13 wie folgt zurück:

=WENN(MOD([Zeitraum],13);MOD([Zeitraum],13);13)

Das Ergebnis sieht wie folgt aus:

Mehrere Beziehungen

In einigen Fällen kann es erforderlich sein, mehrere Beziehungen oder mehrere Datumstabellen zu erstellen. Wenn z. B. mehrere Datumsfelder in der Tabelle Sales-Fakten vorhanden sind, z. B. DateKey, ShipDate und ReturnDate, können alle Beziehungen zum Feld Date in der Kalenderdatumstabelle haben, aber nur eines davon kann eine aktive Beziehung sein. Da DateKey in diesem Fall das Datum der Transaktion und damit das wichtigste Datum darstellt, würde dies am besten als aktive Beziehung dienen. Die anderen haben inaktive Beziehungen.

Die folgende PivotTable berechnet den Gesamtumsatz nach Geschäftsjahr und Geschäftsquartal. Ein Measure mit dem Namen Total Sales mit der Formel Total Sales:=SUM([SalesAmount]) wird in VALUES platziert, und Die Felder FiscalYear und FiscalQuarter aus der Kalenderdatumstabelle werden in ZEILEN platziert.

Diese unkomplizierte PivotTable funktioniert ordnungsgemäß, da wir unseren Gesamtumsatz nach dem Transaktionsdatumin DateKey summieren möchten. Unser Measure Total Sales verwendet die Datumsangaben in DateKey und wird nach Geschäftsjahr und Geschäftsquartal summiert, da eine Beziehung zwischen DateKey in der Tabelle Sales und der Spalte Date in der Kalenderdatumstabelle besteht.

Inaktive Beziehungen

Aber was wäre, wenn wir unseren Gesamtumsatz nicht nach Transaktionsdatum, sondern nach Versanddatum summieren wollten? Wir benötigen eine Beziehung zwischen der Spalte ShipDate in der Tabelle Sales und der Spalte Date in der Tabelle Calendar. Wenn wir diese Beziehung nicht erstellen, basieren unsere Aggregationen immer auf dem Transaktionsdatum. Es können jedoch mehrere Beziehungen bestehen, obwohl nur eine aktiv sein kann, und da das Transaktionsdatum das wichtigste ist, wird die aktive Beziehung mit der Calendar-Tabelle abgerufen.

In diesem Fall verfügt ShipDate über eine inaktive Beziehung, sodass jede Measureformel, die zum Aggregieren von Daten basierend auf Versanddatumen erstellt wird, die inaktive Beziehung mithilfe der USERELATIONSHIP-Funktion angeben muss.

Da beispielsweise eine inaktive Beziehung zwischen der Spalte ShipDate in der Tabelle Sales und der Spalte Date in der Tabelle Calendar besteht, können wir ein Measure erstellen, das den Gesamtumsatz nach Versanddatum addiert. Wir verwenden eine Formel wie diese, um die zu verwendende Beziehung anzugeben:

Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

Diese Formel besagt einfach: Berechnen Sie eine Summe für SalesAmount, filtern Sie jedoch mithilfe der Beziehung zwischen der Spalte ShipDate in der Tabelle Sales und der Spalte Date in der Tabelle Calendar.

Wenn wir nun eine PivotTable erstellen und das Measure Total Sales by Ship Date (Gesamtumsatz nach Versanddatum) in VALUES (Werte) und Fiscal Year and Fiscal Quarter on ROWS (Geschäftsjahr und Geschäftsquartal auf ZEILEN) setzen, wird die gleiche Gesamtsumme angezeigt, aber alle anderen Summen für Geschäftsjahr und Geschäftsquartal unterscheiden sich, da sie auf dem Versanddatum und nicht auf dem Transaktionsdatum basieren.

Die Verwendung inaktiver Beziehungen ermöglicht es Ihnen, nur eine Datumstabelle zu verwenden, erfordert jedoch, dass alle Measures (z. B. Total Sales by Ship Date) in ihrer Formel auf die inaktive Beziehung verweisen. Es gibt eine weitere Alternative, d. h. mehrere Datumstabellen zu verwenden.

Mehrere Datumstabellen

Eine weitere Möglichkeit, mit mehreren Datumsspalten in Ihrer Faktentabelle zu arbeiten, besteht darin, mehrere Datumstabellen zu erstellen und separate aktive Beziehungen zwischen ihnen zu erstellen. Sehen wir uns das Beispiel für die Tabelle Sales noch einmal an. Wir verfügen über drei Spalten mit Datumsangaben, für die Daten aggregiert werden sollen:

• Ein DateKey mit dem Verkaufsdatum für jede Transaktion.

• Ein ShipDate - mit dem Datum und der Uhrzeit, zu dem die verkauften Artikel an den Kunden versendet wurden.

• Ein ReturnDate - mit dem Datum und der Uhrzeit, zu dem ein oder mehrere zurückgegebene Elemente empfangen wurden.

Denken Sie daran, dass das DateKey-Feld mit dem Transaktionsdatum am wichtigsten ist. Wir werden die meisten aggregationen basierend auf diesen Datumsangaben durchführen, sodass wir ganz sicher eine Beziehung zwischen ihr und der Spalte Date in der Tabelle Calendar benötigen. Wenn wir keine inaktiven Beziehungen zwischen ShipDate und ReturnDate und dem Feld Date in der Kalendertabelle erstellen möchten und daher spezielle Measureformeln erfordern, können wir zusätzliche Datumstabellen für Versanddatum und Rückgabedatum erstellen. Wir können dann aktive Beziehungen zwischen ihnen erstellen.

In diesem Beispiel haben wir eine weitere Datumstabelle mit dem Namen ShipCalendar erstellt. Dies bedeutet natürlich auch das Erstellen zusätzlicher Datumsspalten, und da sich diese Datumsspalten in einer anderen Datumstabelle befinden, möchten wir sie so benennen, dass sie von den gleichen Spalten in der Tabelle Calendar unterschieden werden. Beispielsweise haben wir Spalten mit den Namen ShipYear, ShipMonth, ShipQuarter usw. erstellt.

Wenn wir unsere PivotTable erstellen und das Measure Total Sales in VALUES und ShipFiscalYear und ShipFiscalQuarter in ROWS setzen, werden die gleichen Ergebnisse angezeigt, die wir beim Erstellen einer inaktiven Beziehung und eines speziellen berechneten Felds Gesamtumsatz nach Versanddatum gesehen haben.

Jeder dieser Ansätze erfordert sorgfältige Überlegungen. Wenn Sie mehrere Beziehungen mit einer einzelnen Datumstabelle verwenden, müssen Sie möglicherweise spezielle Measures erstellen, die inaktive Beziehungen mit der USERELATIONSHIP-Funktion transitieren. Auf der anderen Seite kann das Erstellen mehrerer Datumstabellen in einer Feldliste verwirrend sein, und da Sie mehr Tabellen im Datenmodell haben, wird mehr Arbeitsspeicher benötigt. Experimentieren Sie mit dem, was für Sie am besten funktioniert.

Vorgehensweise: Festlegen der Date Table-Eigenschaft

1. Wählen Sie im PowerPivot-Fenster die Tabelle Kalender aus.

2. Klicken Sie auf der Registerkarte Entwurf auf Als Datumstabelle markieren.

3. Wählen Sie im Dialogfeld Als Datumstabelle markieren eine Spalte mit eindeutigen Werten und dem Datentyp Date aus.

Konvertieren von Datumsangaben des Textdatentyps in einen Date-Datentyp

In einigen Fällen kann eine Faktentabelle mit Transaktionsdaten Datumsangaben des Textdatentyps enthalten. Das heißt, ein Datum, das als 2012-12-04T11:47:09 angezeigt wird, ist tatsächlich überhaupt kein Datum oder zumindest nicht der Typ des Datums, das Power Pivot verstehen kann. Es ist wirklich nur Text, der sich wie ein Datum liest. Um eine Beziehung zwischen einer Datumsspalte in der Faktentabelle und einer Datumsspalte in einer Datumstabelle zu erstellen, müssen beide Spalten den Datentyp Date aufweisen.

Wenn Sie versuchen, den Datentyp für eine Spalte mit Datumsangaben, die den Textdatentyp aufweisen, in einen Date-Datentyp zu ändern, kann Power Pivot die Datumsangaben in der Regel interpretieren und automatisch in einen true-Datumsdatentyp konvertieren. Wenn Power Pivot keine Datentypkonvertierung durchführen kann, wird ein Typkonfliktfehler angezeigt.

Sie können die Datumsangaben jedoch weiterhin in einen true-Datumsdatentyp konvertieren. Sie können eine neue berechnete Spalte erstellen und eine DAX-Formel verwenden, um das Jahr, den Monat, den Tag, die Uhrzeit usw. aus den Textzeichenfolgen zu analysieren und sie dann wieder so zu verketten, wie Power Pivot als true-Datum lesen kann.

In diesem Beispiel haben wir eine Faktentabelle mit dem Namen Sales in Power Pivot importiert. Sie enthält eine Spalte mit dem Namen DateTime. Werte werden wie folgt angezeigt:

Wenn wir uns den Datentyp auf der Registerkarte Start der Power Pivot-Formatierungsgruppe ansehen, sehen wir, dass es sich um den Datentyp Text handelt.

Wir können keine Beziehung zwischen der DateTime-Spalte und der Date-Spalte in unserer Datumstabelle erstellen, da die Datentypen nicht übereinstimmen. Wenn wir versuchen, den Datentyp in Date zu ändern, erhalten wir einen Typkonfliktfehler:

In diesem Fall konnte Power Pivot den Datentyp nicht von Text in Datum konvertieren. Wir können diese Spalte weiterhin verwenden, aber um sie in einen true Date-Datentyp zu bringen, müssen wir eine neue Spalte erstellen, die den Text analysiert und in einen Wert neu erstellt, den Power Pivot als Date-Datentyp festlegen kann.

Denken Sie daran, aus dem Abschnitt Arbeiten mit Der Zeit weiter oben in diesem Artikel. Es sei denn, ihre Analyse muss auf eine Zeit-von-Tag-Genauigkeitsebene festgelegt werden, sollten Sie Datumsangaben in Ihrer Faktentabelle in eine Tagesgenauigkeit konvertieren. Vor diesem Hintergrund möchten wir, dass die Werte in unserer neuen Spalte auf der Tagesgenauigkeitsebene (ohne Zeit) liegen. Wir können sowohl die Werte in der DateTime-Spalte in einen Date-Datentyp konvertieren als auch die Zeitgenauigkeit mit der folgenden Formel entfernen:

=DATE(LEFT([DateTime],4); MID([DateTime],6;2); MID([DateTime],9;2))

Dadurch erhalten wir eine neue Spalte (in diesem Fall mit dem Namen Date). Power Pivot erkennt sogar die Werte als Datumsangaben und legt den Datentyp automatisch auf Date fest.

Wenn wir das Zeitniveau der Genauigkeit beibehalten möchten, erweitern wir die Formel einfach um die Stunden, Minuten und Sekunden.

=DATE(LEFT([DateTime],4); MID([DateTime],6;2); MID([DateTime],9;2)) +

TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))

Da wir nun über eine Date-Spalte des Date-Datentyps verfügen, können wir eine Beziehung zwischen ihr und einer Datumsspalte in einem Datum erstellen.

Zusätzliche Ressourcen

Datumsangaben in Power Pivot

Berechnungen in Power Pivot

Schnellstart: DAX-Grundlagen in 30 Minuten

Referenz zu Datenanalyseausdrücken

DAX-Ressourcencenter