Wie man in Power BI Datumstabellen erstellt

Wenn Sie mit Power BI arbeiten, werden sie sich früher oder später mit Time-Intelligence-Funktionen konfrontiert sein. Zum Beispiel beim Vergleich von laufenden Zahlen wie Year-To-Date-Berechnungen (YTD-Berechnungen) für die Darstellung Ihres Umsatz im Vergleich zu einem Datum.

Solche Funktionen setzen eine Datumstabelle voraus. Ich empfehle, selbst eine zu erstellen, da die von Power BI automatisch erstellte Datumstabelle in Ihrem Datenmodell nicht dargestellt werden wird.

Mit einer eigenen Tabelle haben Sie volle Kontrolle über die Time-Intelligence-Funktionen.

Standardeinstellung für Time-Intelligence ändern

Als erstes sollten Sie die standardmäßige Verwendung von Time-Intelligence deaktivieren. Statt unbekannte OOTB-Funktionen zu verwenden, über die sie keine Kontrolle haben, ist es besser, die eigenen Daten zu kennen und zu wissen, wie sie zusammenhängen und sich gute Ergebnisse erzielen lassen.

Erste Datumstabelle mit DAX erstellen

Für die Erstellung Ihrer ersten Datumstabelle sind Vorkenntnisse in Power Query der DAX hilfreich.

Ganz zu Anfang müssen Sie entscheiden, welche Zeitspanne Sie abdecken möchten. Ich gehe meistens vom aktuellen Datum aus zwei Jahre in die Vergangenheit und zwei Jahre in die Zukunft.

Vergessen Sie nicht: Eine gute Datumstabelle hilft beim Filtern nach Datum, um den Ladeprozess zu reduzieren.

Starten Sie mit der CALENDAR() Funktion. Dort definieren Sie den Datumsbereich.

Date =
CALENDAR(
    DATE( YEAR( TODAY() ) -2; 1; 1);
    DATE( YEAR( TODAY() ) +2; 12; 31)
)

Wenn Sie die DAX-Abfrage verwenden, erhalten Sie zuerst Ihre Datumstabelle, die vier Jahre spannt. Verwenden Sie immer gute Formatierung. Der DAX-Formatter ist ein gutes Beispiel.

Jetzt denken Sie vielleicht: “Ich hab meine Datumstabelle. Jetzt kann ich mich zurücklehnen!” Aber Moment! Warum erweitern Sie sie nicht, um mehr Vorteile für sich herauszuziehen?

Ändern Sie als letzten Schritt in dieser Phase die Spalte nur auf Datum ab. Das brauchen Sie später in all Ihren Power-BI-Dateien, um sie mit verschiedenen anderen Tabellen zu verbinden.

Machen Sie Ihre Datumstabelle benutzerfreundlicher

Die gerade erstellte Datumstabelle enthält viele Daten. Sie als Filter für Ihre Power-BI-Berichte zu verwenden, wird den Endnutzer verwirren, weil sie so lang ist. Daten kennen wir als Kombination von Tag, Monat und Jahr, vielleicht gruppiert bei Quartal oder andere nützliche Gruppierungen.

Weshalb erweitern Sie Ihre kleine Datumstabelle nicht, um diese Zahlen aufzunehmen?

Fügen Sie zunächst neue Spalten hinzu, um Jahre, Monate und Tage auf Basis Ihrer Datums-Spalten zu generieren. Wie in Microsot Excel können Sie DAX-Funktionen verwenden, um diese Spalten sehr einfach zu generieren.

Sie geben mir bestimmt Recht, wenn ich sage, dass die Monats-Spalte nicht toll aussieht. Nutzer erwarten den Namen des Monats, nicht eine Zahl. Um dieses Problem zu lösen, können Sie eine benutzerfreundlichere Spalte einfügen.

Dafür müssen Sie die Zahlenwerte zerlegen und Sie mit Switch-Befehlen übersetzen. Die bewährteste Methode ist, das Standard-Statement in jedem Switch zu benutzen (man weiß nie, was passieren kann).

Month Name =
SWITCH(
    ‚Date'[Month];
    1; „Jan“;
    2; „Feb“;
    3; „Mar“;
    4; „Apr“;
    5; „May“;
    6; „Jun“;
    7; „Jul“;
    8; „Aug“;
    9; „Sep“;
    10; „Oct“;
    11; „Nov“;
    12; „Dec“;
    „oops“
)

Wenn Sie sich Ihre Tabelle nochmal ansehen, sieht sie schon viel besser aus.

Kombinierte Spalten hinzufügen

Bereit für den Feinschliff? Neben Monatsnamen müssen Sie auch die Werte für Monate und das Jahr kombinieren, um sinnvolle jährliche Überblicke zu generieren. Als letzten Schritt sollten Sie Ihre Spalten sortieren. Standardmäßigen werden sie alphabetisch geordnet, was bei Daten vollkommen unnütz ist.

Month Year = ‚Date'[Month Name] & “ “ & ‚Date'[Year]

“&” verbindet die Werte, genau wie in Excel.

Stellen Sie nun eine Reihenfolge für Ihre Spalten auf, damit Sie in Ihren Berichten korrekt dargestellt werden. Generieren Sie dafür order-by Spalten auf die gleiche Weise und kombinieren Sie die Nummern, nach denen geordnet werden soll.

Month Year No = ‚Date'[Year] & ‚Date'[Month]

Wenn alle Spalten fertig sind, können Sie die Kriterien definieren, nach denen sortiert werden soll, und zwar auf Basis der Nummer-Spalten. “Monatsname” wird nach “Monat” sortiert und die neue “Monat Jahr”-Spalte wird nach “Monat Jahr Nr.” geordnet.

Sie können auch eine kleine Rechnung für das Quartal erstellen, indem Sie die Monatszahl durch vier teilen.

Quarter = QUOTIENT(‚Date'[Month];4)+1

Mit der gleichen Logik wie zuvor, fügen Sie eine Spalte mit dem Quartalsname hinzu, so dass nicht nur der Nummernwert dargestellt wird:

Quarter Name = „Qtr “ & QUOTIENT(‚Date'[Month];4)+1

In der Tabelle können Sie die korrekte Reihenfolge nicht regeln. Hierfür benötigen Sie ein einfaches Diagramm.

Das Prüfen der Daten in einem Diagramm zeigt Ihnen, dass die Reihenfolge korrekt ist. April ist der letzte Monat in dieser Datenserie.

Datums-Hierarchie hinzufügen

Zuletzt müssen Sie Datums-Hierarchie definieren, die Sie für Ihr Datenmodell benutzen möchten. Sie wird die Drill-down-Funktion auf Basis Ihrer Datum-Zeit aktivieren. Wählen Sie in der Datumsansicht die mit der Hierarchie verbundenen Spalte aus und fügen Sie sie zu einer neuen Hierarchie hinzu. Klicken Sie mit der rechten Maustaste auf die Spalte und fügen Sie eine Hierarchie hinzu.

Nachdem die Hierarchie hinzugefügt wurde, können Sie alle anderen Spalten, die Sie nicht benötigen, verbergen. Die finale Datums-Tabelle sollte so aussehen:

Power Query basierende Datumstabelle

Bis jetzt habe ich Ihnen gezeigt, wie Sie DAX nutzen, um diese Datumstabellen zu erstellen. Sie können jedoch auch Power Query nutzen, falls Sie sich damit besser auskennen.

Wechseln Sie dafür in den “Edit Queries”-Bereich und beginnen Sie damit, eine neue leere Custom-Query hinzuzufügen. Im erweiterten Editor schreiben Sie Ihre Power-Query:

let
    Start = Date.StartOfYear(Date.AddYears(Date.From(DateTimeZone.UtcNow()),-2)),
    End = Date.StartOfYear(Date.AddYears(Date.From(DateTimeZone.UtcNow()), 2)),
    DayCount = Duration.Days(Duration.From(End – Start)),
    Source = List.Dates(Start, DayCount, #duration(1,0,0,0)),
    #“Converted to Table“ = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #“Changed Type“ = Table.TransformColumnTypes(#“Converted to Table“,{{„Column1“, type date}}),
    #“Renamed Columns“ = Table.RenameColumns(#“Changed Type“,{{„Column1“, „Datekey“}})
in
    #“Renamed Columns“

Diese Tabelle wird ähnlich aussehen und eine Datumsspalte beinhalten. Mit den standardmäßigen “From Date & Time”-Funktionen fügen Sie die verschiedenen Spalten hinzu wie bei DAX Queries.

In der “Add Column”-Leiste können sie die verschiedenen Spalten per Klick hinzufügen, um Quartal, Monat oder Tag anzuzeigen.

Für eine sinnvollere Reihenfolge können Sie mit “Add custom column” Zahlenspalten hinzufügen.

Month Year Name = [Month Name] & “ “ & Text.From([Year])

Letzte Power Query Datumstabelle

Sie haben jetzt gesehen, wie man eine Datumstabelle mit DAX oder Power Query erstellt. Entscheiden Sie selbst, welche Sie nutzen mötchten.

Sie können die Datumstabelle in sämtlichen Power-BI-Berichten wiederverwenden und sie Schritt für Schritt je nach Ihren Anforderungen ausbauen.

Ich habe Ihnen gezeigt, wie Sie eine grundlegende Datumstabelle erstellen. Sie müssen nur die Werte hinzufügen, die in Ihrem Implementations-Szenarios Sinn ergeben.

let
    Start = Date.StartOfYear(Date.AddYears(Date.From(DateTimeZone.UtcNow()),-2)),
    End = Date.StartOfYear(Date.AddYears(Date.From(DateTimeZone.UtcNow()), 2)),
    DayCount = Duration.Days(Duration.From(End – Start)),
    Source = List.Dates(Start, DayCount, #duration(1,0,0,0)),
    #“Converted to Table“ = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #“Changed Type“ = Table.TransformColumnTypes(#“Converted to Table“,{{„Column1“, type date}}),
    #“Renamed Columns“ = Table.RenameColumns(#“Changed Type“,{{„Column1“, „Datekey“}}),
    #“Inserted Year“ = Table.AddColumn(#“Renamed Columns“, „Year“, each Date.Year([Datekey]), Int64.Type),
    #“Inserted Quarter“ = Table.AddColumn(#“Inserted Year“, „Quarter“, each Date.QuarterOfYear([Datekey]), Int64.Type),
    #“Inserted Month“ = Table.AddColumn(#“Inserted Quarter“, „Month“, each Date.Month([Datekey]), Int64.Type),
    #“Inserted Month Name“ = Table.AddColumn(#“Inserted Month“, „Month Name“, each Date.MonthName([Datekey]), type text),
    #“Inserted Day“ = Table.AddColumn(#“Inserted Month Name“, „Day“, each Date.Day([Datekey]), Int64.Type),
    #“Inserted Day Name“ = Table.AddColumn(#“Inserted Day“, „Day Name“, each Date.DayOfWeekName([Datekey]), type text),
    #“Added Custom“ = Table.AddColumn(#“Inserted Day Name“, „Month Year Name“, each [Month Name] & “ “ & Text.From([Year]))
in
    #“Added Custom“