Datumstabelle in Power BI von Grund auf erstellen

Eine Datumstabelle bauen Sie, indem Sie mit DAX oder mit einer M-Abfrage in Power Query eine Zeile pro Tag erzeugen, dann Spalten für Jahr, Monat, Quartal und Geschäftsjahr ergänzen, für die Textfelder „Nach Spalte sortieren“ setzen und die Tabelle als offizielle Datumstabelle Ihres Modells markieren. Dieser Beitrag baut eine vollständige Tabelle von Anfang bis Ende, in DAX und in Power Query. Am Ende haben Sie eine Tabelle, die für Zeitintelligenz-Funktionen bereit ist. Einen Überblick über alle Methoden, auch den Import eines fertigen Kalenders, finden Sie in unserer Anleitung zum Erstellen von Datumstabellen in Power BI.

Was die Tabelle braucht, damit sie funktioniert

Die Zeitintelligenz-Funktionen von Power BI, etwa TOTALYTD und SAMEPERIODLASTYEAR, arbeiten nur dann korrekt auf einer Tabelle, wenn drei Bedingungen erfüllt sind:

1. Eine Zeile pro Kalendertag, ohne Lücken und ohne Duplikate.

2. Eine Datumsspalte mit echtem Datentyp date, nicht Text und nicht Datum/Uhrzeit mit Zeitanteil.

3. Volle Jahre im Bereich, weil ein unvollständiges Jahr Vorjahresvergleiche an den Rändern verfälscht.

Eine berechnete Tabelle oder eine M-Abfrage, die jeden Tag zwischen zwei Daten auflistet, erfüllt alle drei automatisch. Deshalb beginnen beide Methoden unten damit.

Methode 1: Mit DAX bauen

Öffnen Sie die Registerkarte Modellierung und wählen Sie Neue Tabelle. Geben Sie diese Formel ein:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2030, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month Name", FORMAT ( [Date], "MMMM" ),
    "Quarter", "Q" & QUARTER ( [Date] ),
    "Year Quarter", YEAR ( [Date] ) & "-Q" & QUARTER ( [Date] ),
    "Year Month", FORMAT ( [Date], "YYYY-MM" ),
    "Day Of Week Number", WEEKDAY ( [Date], 2 ),
    "Day Name", FORMAT ( [Date], "dddd" ),
    "Is Weekend", WEEKDAY ( [Date], 2 ) > 5,
    "Fiscal Month", MOD ( MONTH ( [Date] ) + 8, 12 ) + 1,
    "Fiscal Quarter", "FQ" & ( INT ( MOD ( MONTH ( [Date] ) + 8, 12 ) / 3 ) + 1 ),
    "Fiscal Year", IF ( MONTH ( [Date] ) >= 4, YEAR ( [Date] ) + 1, YEAR ( [Date] ) )
)

CALENDAR erzeugt die rohe Liste der Datumswerte. ADDCOLUMNS ergänzt jedes Attribut, nach dem Sie filtern oder gruppieren, einmal bei der Aktualisierung der Tabelle statt zur Abfragezeit. Passen Sie die beiden Daten in CALENDAR an Ihren tatsächlichen Datenbereich an, mit etwas Spielraum an beiden Enden.

Die Spalten für das Geschäftsjahr gehen davon aus, dass es im vierten Kalendermonat beginnt, und benennen jedes Geschäftsjahr nach dem Kalenderjahr, in dem es endet. Das + 8 verschiebt die Monate so, dass der vierte Monat zum Geschäftsmonat 1 wird. Für ein Geschäftsjahr, das in Monat n beginnt, nutzen Sie + (12 - n) in den Formeln für Geschäftsmonat und -quartal und >= n in der Formel für das Geschäftsjahr. Addieren statt Subtrahieren hält die Zahl positiv. Das ist in M wichtig: Number.Mod liefert bei negativer Eingabe ein negatives Ergebnis, das MOD von DAX dagegen nicht.

Methode 2: Mit Power Query bauen

Wählen Sie in Power BI Desktop Daten transformieren, um den Power Query Editor zu öffnen, dann Start > Neue Quelle > Leere Abfrage. Öffnen Sie den Erweiterten Editor und fügen Sie ein:

let
    StartDate = #date(2020, 1, 1),
    EndDate = #date(2030, 12, 31),
    DayCount = Duration.Days(EndDate - StartDate) + 1,
    DateList = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),
    Source = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),
    ChangedType = Table.TransformColumnTypes(Source, {{"Date", type date}}),
    AddYear = Table.AddColumn(ChangedType, "Year", each Date.Year([Date]), Int64.Type),
    AddMonthNumber = Table.AddColumn(AddYear, "Month Number", each Date.Month([Date]), Int64.Type),
    AddMonthName = Table.AddColumn(AddMonthNumber, "Month Name", each Date.MonthName([Date]), type text),
    AddQuarter = Table.AddColumn(AddMonthName, "Quarter", each "Q" & Text.From(Date.QuarterOfYear([Date])), type text),
    AddYearMonth = Table.AddColumn(AddQuarter, "Year Month", each Date.ToText([Date], "yyyy-MM"), type text),
    AddDayName = Table.AddColumn(AddYearMonth, "Day Name", each Date.DayOfWeekName([Date]), type text),
    AddDayOfWeekNumber = Table.AddColumn(AddDayName, "Day Of Week Number", each Date.DayOfWeek([Date], Day.Monday) + 1, Int64.Type),
    AddIsWeekend = Table.AddColumn(AddDayOfWeekNumber, "Is Weekend", each [Day Of Week Number] > 5, type logical),
    AddFiscalMonth = Table.AddColumn(AddIsWeekend, "Fiscal Month", each Number.Mod([Month Number] + 8, 12) + 1, Int64.Type),
    AddFiscalQuarter = Table.AddColumn(AddFiscalMonth, "Fiscal Quarter", each "FQ" & Text.From(Number.IntegerDivide([Fiscal Month] - 1, 3) + 1), type text),
    AddFiscalYear = Table.AddColumn(AddFiscalQuarter, "Fiscal Year", each if [Month Number] >= 4 then [Year] + 1 else [Year], Int64.Type)
in
    AddFiscalYear

Benennen Sie die Abfrage in Date um und wählen Sie Schließen & übernehmen. Jeder Schritt Table.AddColumn baut auf dem vorigen auf, sodass die fertige Tabelle jede Spalte der DAX-Version enthält.

Das Menü Spalte hinzufügen > Datum in Power Query, ein schneller Weg zu Datumsattributen, wenn Sie die Basisliste der Daten von Hand bauen.

„Nach Spalte sortieren“ festlegen

FORMAT und Date.MonthName liefern Text. Deshalb sortiert Month Name standardmäßig in Textreihenfolge, nicht in Kalenderreihenfolge, und Day Name ebenso. So beheben Sie beides:

1. Wählen Sie die Spalte Month Name. Wählen Sie auf der Registerkarte Spaltentools die Option Nach Spalte sortieren und dann Month Number.

2. Wählen Sie die Spalte Day Name, dann Nach Spalte sortieren und Day Of Week Number.

Slicer und Achsen, die aus einer der beiden Spalten gebaut sind, sortieren jetzt in Kalenderreihenfolge statt alphabetisch.

Die Tabelle als Datumstabelle markieren

Klicken Sie im Bereich Felder mit der rechten Maustaste auf die Tabelle, wählen Sie Als Datumstabelle markieren und dann die Spalte Date. Power BI prüft dann, ob die Spalte die oben genannten Bedingungen erfüllt, und nutzt diese Tabelle für Zeitintelligenz statt einer automatischen Datumstabelle.

Auto-Datum/Uhrzeit ausschalten

Auto-Datum/Uhrzeit erstellt hinter jeder Datums- oder Datum/Uhrzeit-Spalte im Modell einen versteckten Kalender. Mit einer echten Datumstabelle ist dieser versteckte überflüssig und macht das Modell ohne Nutzen größer.

1. Gehen Sie zu Datei > Optionen und Einstellungen > Optionen, wählen Sie die Registerkarte Aktuelle Datei, dann Daten laden, und entfernen Sie das Häkchen bei Auto-Datum/Uhrzeit für diese Datei.

2. Wählen Sie auf der Registerkarte Global die Option Daten laden und entfernen Sie das Häkchen bei Auto-Datum/Uhrzeit für neue Dateien, damit künftige Dateien ohne starten.

Wenn Sie es für die aktuelle Datei ausschalten, werden die versteckten Tabellen gelöscht. Jedes Visual und jedes Measure, das eine automatische Datumshierarchie nutzte, funktioniert dann nicht mehr. Bauen Sie diese daher zuerst auf den Spalten Ihrer Datumstabelle neu und schalten Sie die Einstellung erst dann aus.

Ist die Tabelle markiert und Auto-Datum/Uhrzeit aus, ist sie bereit für Zeitintelligenz-Measures wie Jahr bis heute und Vorjahresvergleiche. Das DAX für diese Tabelle finden Sie in unserem Leitfaden zu Zeitintelligenz in Power BI und unter YTD-Berechnungen erstellen.

Quellen

• Date tables in Power BI Desktop - Microsoft Learn

• Auto date/time in Power BI Desktop - Microsoft Learn