Erstellen einer Datumstabelle
Bei der Berichterstellung in Power BI ist es eine gängige Geschäftsanforderung, Berechnungen auf Grundlage von Daten und Uhrzeiten durchzuführen. Organisationen möchten wissen, was das Unternehmen in bestimmten Monaten, Quartalen, Geschäftsjahren usw. geleistet hat. Aus diesem Grund ist es von entscheidender Bedeutung, dass diese zeitorientierten Werte ordnungsgemäß formatiert sind. Power BI erkennt Datenspalten und Tabellen automatisch. Es können jedoch Situationen auftreten, in denen Sie zusätzliche Schritte ausführen müssen, um die Datumsangaben in dem von Ihrem Unternehmen benötigten Format zu erhalten.
Angenommen, Sie entwickeln beispielsweise Berichte für die Vertriebsabteilung Ihrer Organisation. Die Datenbank enthält Tabellen für Verkäufe, Aufträge, Produkte und vieles mehr. Sie bemerken, dass viele dieser Tabellen (einschließlich der „Sales“- und „Orders“-Tabellen) wie in den Spalten ShipDate und OrderDate in den Tabellen „Sales“ und „Orders“ angezeigt eigene Datumsspalten enthalten. Sie sind dafür zuständig, eine Tabelle für die Gesamtumsätze und Aufträge nach Jahr und Monat zu erstellen. Wie können Sie ein Visual mit mehreren Tabellen erstellen, die jeweils auf ihre eigenen Datumsspalten verweisen?
Sie können zum Lösen dieses Problems eine allgemeine Datumstabelle erstellen, die von mehreren Tabellen verwendet werden kann. Im folgenden Abschnitt wird erläutert, wie Sie diese Aufgabe in Power BI ausführen können.
Erstellen allgemeiner Datumstabellen
Sie können eine allgemeine Datumstabelle auf folgende Art und Weise erstellen:
Quelldaten
DAX
Power Query
Quelldaten
Gelegentlich verfügen Quelldatenbanken und Data Warehouse-Datenbanken bereits über eigene Datumstabellen. Wenn der Administrator, der die Datenbank entworfen hat, seine Arbeit gründlich erledigt hat, können diese Tabellen verwendet werden, um die folgenden Aufgaben auszuführen:
Identifizieren von Urlaubstagen der Mitarbeiter im Unternehmen
Unterscheiden von Kalender- und Geschäftsjahren
Bestimmen von Wochenenden und Wochentagen
Quelldatentabellen sind ausgereift und sofort einsatzbereit. Wenn Sie über eine solche Tabelle verfügen, verwenden Sie sie in Ihrem semantischen Modell. Verwenden Sie keine der anderen in diesem Abschnitt beschriebenen Methoden. Es wird empfohlen, eine Quelldatentabelle zu verwenden, da diese wahrscheinlich auch für andere Tools freigegeben wird, die Sie zusätzlich zu Power BI verwenden.
Wenn Sie nicht über eine Quelldatentabelle verfügen, gibt es andere Möglichkeiten, eine allgemeine Datumstabelle zu erstellen.
DAX
Sie können die DAX-Funktionen (Data Analysis Expression) „CALENDARAUTO()“ und „CALENDAR()“ verwenden, um eine allgemeine Datumstabelle zu erstellen. Die Funktion „CALENDAR()“ gibt einen zusammenhängenden Datumsbereich basierend auf einem Start- und Enddatum zurück, die als Argumente in die Funktion eingegeben werden. Alternativ gibt die Funktion „CALENDARAUTO()“ einen zusammenhängenden, kompletten Datumsbereich zurück, der automatisch von Ihrem semantischen Modell bestimmt wird. Das Startdatum wird als frühestes Datum ausgewählt, das in Ihrem semantischen Modell vorhanden ist, und das Enddatum ist das letzte im semantischen Modell verfügbare Datum. Dazu kommen die aufgefüllten Daten für den Geschäftsmonat, den Sie als Argument in die „CALENDARAUTO()“-Funktion einschließen können. Im Rahmen dieses Beispiels wird die „CALENDAR()“-Funktion verwendet, da Sie nur die Daten für die zehn Jahre ab dem 31. Mai 2011 (dem ersten Tag, ab dem diese Daten nachverfolgt wurden) anzeigen möchten.
Klicken Sie in Power BI Desktop auf Neue Tabelle, und geben Sie dann die folgende DAX-Formel ein:
Dates = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))
Nun verfügen Sie über eine Spalte mit Datumsangaben, die Sie verwenden können. Diese Spalte ist jedoch nicht sehr umfangreich. Sie möchten außerdem, dass nur Spalten für ein Jahr, einen Monat, eine Monatsnummer, die Woche eines Jahres oder einen Wochentag angezeigt werden. Dies erreichen Sie, indem Sie auf dem Menüband auf Neue Spalte klicken und die folgende DAX-Gleichung eingeben, mit der das Jahr aus der Datumstabelle abgerufen wird.
Year = YEAR(Dates[Date])
Sie können den gleichen Prozess zum Abrufen der Monatsnummer, der Wochennummer und des Wochentags ausführen:
MonthNum = MONTH(Dates[Date])
WeekNum = WEEKNUM(Dates[Date])
DayoftheWeek = FORMAT(Dates[Date], "DDDD")
Wenn Sie fertig sind, enthält die Tabelle die Spalten, die in der folgenden Abbildung dargestellt sind.
Sie haben jetzt mithilfe von DAX eine allgemeine Datumstabelle erstellt. Bei diesem Vorgang wird nur die neue Tabelle dem semantischen Modell hinzugefügt. Sie müssen weiterhin Beziehungen zwischen der Datumstabelle und den „Sales“- und „Order“-Tabellen einrichten und dann die Tabelle als offizielle Datumstabelle des semantischen Modells kennzeichnen. Bevor Sie diese Aufgaben ausführen, sollten Sie jedoch sicherstellen, dass Sie eine andere Möglichkeit zum Erstellen einer allgemeinen Datumstabelle in Erwägung gezogen haben: Power Query.
Power Query
Mit Power Query M können Sie die Entwicklungssprache verwenden, mit der in Power Query Abfragen erstellt werden, um eine allgemeine Datumstabelle zu erstellen.
Klicken Sie in Power BI Desktop auf Daten transformieren, woraufhin Sie zu Power Query weitergeleitet werden. Klicken Sie im Leerraum des Bereichs Abfragen auf der linken Seite mit der rechten Maustaste, um das folgende Dropdownmenü zu öffnen, in dem Sie dann auf Neue Abfrage > Leere Abfrage klicken.
Geben Sie in der nun angezeigten Ansicht Neue Abfrage die folgende M-Formel ein, um eine Kalendertabelle zu erstellen:
= List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))
In Bezug auf Ihre Vertriebsdaten soll das Startdatum das früheste Datum widerspiegeln, das in Ihren Daten enthalten ist: 31. Mai 2011. Außerdem möchten Sie die Datumsangaben für die nächsten zehn Jahre einschließlich in der Zukunft liegender Datumsangaben anzeigen. Durch diese Vorgehensweise wird sichergestellt, dass diese Tabelle nicht neu erstellt werden muss, wenn neue Vertriebsdaten eingefügt werden. Sie können die Dauer auch ändern. In diesem Fall benötigen Sie einen Datenpunkt für jeden Tag, aber Sie können auch nach Stunden, Minuten und Sekunden erhöhen. In der folgenden Abbildung wird das Ergebnis veranschaulicht.
Nach erfolgreicher Ausführung des Prozesses bemerken Sie, dass Sie anstelle einer Tabelle mit Daten eine Liste mit Daten erstellt haben. Navigieren Sie zum Menüband auf der Registerkarte Transformieren, und klicken Sie auf Konvertieren > In Tabelle, um diesen Fehler zu beheben. Wie der Name bereits vermuten lässt, konvertiert dieses Feature die Liste in eine Tabelle. Sie können die Spalte auch in DateCol umbenennen.
Als Nächstes möchten Sie in der neuen Tabelle Spalten hinzufügen, um Datumsangaben mit Jahr, Monat, Woche und Tag anzuzeigen, sodass Sie eine Hierarchie in Ihrem Visual erstellen können. Ihre erste Aufgabe besteht darin, den Spaltentyp zu ändern, indem Sie auf das Symbol neben dem Namen der Spalte klicken und im daraufhin angezeigten Dropdownmenü den Typ Datum auswählen.
Nachdem Sie den Typ Datum ausgewählt haben, können Sie Spalten für Jahr, Monat, Woche und Tag hinzufügen. Navigieren Sie zu Spalte hinzufügen, und wählen Sie im Dropdownmenü unter Datum die Option Jahr aus, wie in der folgenden Abbildung dargestellt.
Wie Sie sehen, hat Power BI eine Spalte mit allen Jahren hinzugefügt, die aus DateCol abgerufen werden.
Führen Sie die gleichen Schritte für Monate, Wochen und Tage aus. Wenn Sie fertig sind, enthält die Tabelle die Spalten, die in der folgenden Abbildung dargestellt sind.
Sie haben nun erfolgreich mit Power Query eine allgemeine Datumstabelle erstellt.
In den vorherigen Schritten haben Sie erfahren, wie Sie die Tabelle in das semantische Modell einfügen. Nun müssen Sie die Tabelle als die offizielle Datumstabelle markieren, damit Power BI sie für alle zukünftigen Werte erkennen und sicherstellen kann, dass die Formatierung korrekt ist.
Markieren als offizielle Datumstabelle
Ihre erste Aufgabe beim Markieren der Tabelle als die offizielle Datumstabelle ist das Finden der neuen Tabelle im Bereich Felder. Klicken Sie mit der rechten Maustaste auf den Namen der Tabelle, und wählen Sie dann Als Datumstabelle markieren aus, wie in der folgenden Abbildung dargestellt.
Wenn Sie die Tabelle als Datumstabelle markieren, führt Power BI Prüfungen durch, um sicherzustellen, dass die Daten keine NULL-Werte enthalten, eindeutig sind und durchgängige Datumswerte für einen bestimmten Zeitraum enthalten. Sie können auch bestimmte Spalten in der Tabelle auswählen, um sie als Datum zu markieren. Dies kann hilfreich sein, wenn Ihre Tabelle über viele Spalten verfügt. Klicken Sie mit der rechten Maustaste auf die Tabelle, wählen Sie Als Datumstabelle markieren und dann Datumstabelleneinstellungen aus. Das folgende Fenster wird angezeigt, in dem Sie auswählen können, welche Spalte als Datum markiert werden soll.
Durch das Klicken auf Als Datumstabelle markieren werden automatisch generierte Hierarchien aus dem Feld Datum der Tabelle entfernt, die Sie als Datumstabelle markiert haben. Bei anderen Datumsfeldern ist die automatische Hierarchie weiterhin vorhanden, bis Sie eine Beziehung zwischen dem Feld und der Datumstabelle erstellen oder das Feature Autom. Datum/Uhrzeit deaktivieren. Sie können eine Hierarchie manuell zu Ihrer allgemeinen Datumstabelle hinzufügen, indem Sie im Bereich Felder mit der rechten Maustaste auf die Spalte für Jahr, Monat, Woche oder Tag klicken und dann Neue Hierarchie auswählen. Dieser Vorgang wird später in diesem Modul näher erläutert.
Erstellen Ihres Visuals
Sie müssen eine Beziehung zwischen dieser neuen allgemeinen Datumstabelle und den Tabellen „Sales“ und „Order“ erstellen, um ein Visual für die beiden letzteren Tabellen erstellen zu können. Dies ermöglicht es Ihnen, Visuals mithilfe der neuen Datumstabelle zu erstellen. Navigieren Sie zum Abschluss dieser Aufgabe zur Registerkarte Modell >Beziehungen verwalten. Dort können Sie mithilfe der Spalte OrderDate Beziehungen zwischen der allgemeinen Datumstabelle und den Tabellen „Order“ und „Sales“ erstellen. Der folgende Screenshot zeigt ein Beispiel für eine solche Beziehung.
Nach den Beziehungen können Sie Ihr Visual Gesamtumsatz und Bestellmenge nach Zeit mit der allgemeinen Datumstabelle erstellen, die Sie mit der DAX- oder Power Query-Methode generiert haben.
Für die Ermittlung des Gesamtumsatzes müssen Sie alle Verkäufe addieren, da die Spalte Amount in der Tabelle „Sales“ nur den Umsatz für die einzelnen Verkäufe und nicht den Gesamtumsatz enthält. Sie können diese Aufgabe mit der folgenden Measureberechnung abschließen, die später erläutert wird. Für die Erstellung des Measures wird die folgende Berechnung durchgeführt:
#Total Sales = SUM(Sales[‘Amount’])
Anschließend können Sie eine Tabelle erstellen, indem Sie zur Registerkarte Visualisierungen zurückkehren und auf das Tabellenvisual klicken. Sie möchten die Gesamtanzahl von Aufträgen und den Umsatz nach Jahr und Monat anzeigen. Daher möchten Sie nur die Jahres- und die Monatsspalte der Datumstabelle, die OrderQty-Spalte und das Measure #TotalSales verwenden. Wenn Sie mehr über Hierarchien erfahren haben, können Sie auch eine Hierarchie erstellen, mit der Sie einen Drilldown von Jahren zu Monaten ausführen können. In diesem Beispiel können Sie sie nebeneinander anzeigen. Sie haben nun erfolgreich ein Visual mit einer allgemeinen Datumstabelle erstellt.
