Datumsfilter: Wann ist eigentlich heute?

    Auswertungen sollen am besten bis zum Ende des letzten Monat gehen. Bei Aktualisierungen soll der Filter automatisch angepasst werden. Wann ist aber heute? In diesem Beitrag zeige ich dir, was für mich “Heute” bedeuten kann.

    Slicer auf Heute stellen

    Viele unserer Kunden möchten zeitliche Einschränkungen zentral einstellen. Die erste Seite dieser Reporte dienen als eine Art Cockpit. Dort verwenden sie meist einen Slicer. Bei einer Aktualisierung der Daten wollen sie diesen Filter aber nicht immer manuell anpassen und auf den aktuellen Monat und Jahr stellen, bevor sie ihre Auswertung weitergeben.

    Hier verwende ich einen Trick: Du baust in einer Datumstabelle eine berechnete Spalte beispielsweise für den im Slicer angezeigten Monat. Für jeden Monat soll dort das der Name des Monats stehen. Nur für den aktuellen Monat soll dort “Aktueller Monat” stehen:

    MonthForSlicer = if(Calender[Year] = year(Today()) && Calendar[Month] = Month(Today());“Aktueller Monat“; Calendar[MonthName])

    Im Slicer verwendest du diese berechnete Spalte und wählst „Aktueller Monat“ aus. Bei der nächsten Datenaktualisierung ist “Aktueller Monat” immer noch ausgewählt (die Slicer behalten die Auswahl). Nur ist das jetzt eben wieder der aktuelle Monat.

    Diesen Trick haben wir in einem früheren Artikel schon einmal ausführlicher beschrieben.

    Year-To-Date

    Bei YTD-Auswertungen (Year-To-Date) ist der Begriff “Heute” relevant. Vergleichen wir den Umsatz im laufenden Jahr mit dem Umsatz des Vorjahres dürfen wir den Umsatz des laufenden Jahres natürlich nur mit dem Umsatz des vorherigen Jahres in der gleichen Zeitspanne vergleichen. Für YTD gibt es vordefinierte Funktionen, wie beispielsweise DATESYTD:

    Umsatz YTD := CALCULATE(SUM(FaktAuftrag[Umsatz]); DATESYTD(DimDatum[Datum]))

    DATESYTD gibt alle Datums-Zeilen wieder, die vor dem aktuell ausgewählten maximalen Datum liegen. Damit kann man leicht den monatlichen Umsatz und den rollierenden Umsatz darstellen:

    Tabelle mit YTD-Umsätzen

    Auf einen Blick

    Die Information mit der Tabelle ist für ein Dashboard zu viel Information. Eigentlich möchte man den Umsatz im aktuellen Jahr darstellen, daneben den Umsatz in der gleichen Periode des Vorjahres, die absolute und die relative Differenz. In Power BI verwende ich dazu Cards.

    Umsatzkennzahlen auf einen Blick

    Die Measures sind wie folgt definiert:

    Umsatz YTD = CALCULATE(sum(‚Umsätze'[Umsatz]); DATESYTD(DimDatum[Date]))

    Umsatz YTD PY = CALCULATE([Umsatz YTD]; SAMEPERIODLASTYEAR(DimDatum[Date]))

    Diff = [Umsatz YTD] – [Umsatz YTD PY]

    Diff% = DIVIDE([Diff];’Umsätze'[Umsatz YTD PY])

    Die Berechnung hat Nachteile:

    • Im Report oder in jeder Karte muss das aktuelle Jahr und der aktuelle Monat gefiltert und bei jeder Aktualisierung angepasst werden (über den Trick aus dem ersten Abschnitt auch automatisch machbar)
    • Mit jedem Tag werden die Kennzahlen schlechter, wenn die Daten nicht aktualisiert werden. Das stimmt natürlich nicht unbedingt: Wenn man den Trick aus dem ersten Abschnitt verwendet und die Daten nicht aktualisiert, bleibt “Heute” oder „Aktueller Monat“ jeden Tag gleich.

    YTD ohne Filter

    Um die Kennzahlen ohne Filter verwenden zu können, definiere ich mir immer ein YTD-Datum. Im einfachsten Fall nehme ich “Heute”:

    [YTD Date] = TODAY()

    Damit kann ich mein YTD-Measure so definieren:

    Umsatz YTD = CALCULATE(sum(‚Umsätze'[Umsatz]); FILTER(all(DimDatum); DimDatum[Date] >= DATE(YEAR([YTD Date]);1;1) && DimDatum[Date] <= [YTD Date]))

    Das Measure ignoriert alle Datumseinschränkungen und berechnet den Umsatz vom 1.1. des aktuellen Jahres (Jahr des YTD-Date) bis zum definierten YTD-Datum. Andere Filter, wie Niederlassungen, Sparten, oder Kategorien bleiben natürlich erhalten. Die anderen Measures für Vorjahreswerte und Differenzen bleiben gleich.

    Nachteil des YTD-Datum: Mit jedem Tag werden die Kennzahlen schlechter. Obwohl die Daten nicht aktualisiert wurden.

    Heute: Datum der letzten Aktualisierung

    Das Datum der letzten Aktualisierung wäre schön. Das gibt aber weder Power Query noch Power BI (oder Excel Power Pivot) als Information zurück. Wie auch: Beim Aktualisieren kann man ja jede Abfrage unabhängig aktualisieren. Damit kann gibt es “das Aktualisierungsdatum” nicht.

    Ich gehe aber trotzdem davon aus, dass wenn Berichte aktualisiert werden, dass alle Quellen neu geladen werden. Für das Aktualisierungsdatum baue ich eine einfache Power Query Abfrage:

    Damit kann ich das YTD-Datum so definieren:

    [YTD Date] := MAX(‚UpdateInfo'[Update Date])

    Ab jetzt ändern sich die YTD-Measures aus dem letzten Abschnitt nicht täglich, sondern nur bei Aktualisierung.

    Mit diesem Measure kann ich außerdem in der Kopf- oder Fußzeile des Berichtes angeben, wann der Report zum letzten Mal aktualisiert wurde. Für den Leser des Reports ganz praktisch.

    Heute: letzter Fakt

    Oftmals werden Daten zur Auswertung nicht direkt aus einer produktiven Datenbank geladen, sondern in einer anderen Datenbank vorgehalten. Wenn diese nicht aktualisiert wurde greifen wir auf alte Daten zu. Wenn man das Aktualisierungsdatum als YTD-Datum verwendet, werden die Umsatzkennzahlen trotzdem nicht korrekt dargestellt. Besser wäre eigentlich das YTD-Datum an den Bewegungsdaten festzumachen:

    [YTD Date] = MAXX(ALL(FaktUmsatz);FaktUmsatz[Datum])

    ALL() brauchen wir hier, da das Measure  in anderen DAX-Formeln verwendet werden kann, in denen bereits Filter angewandt sind.

    Ein wenig unschön daran ist, dass der Filter dadurch beispielsweise am 28. April eines Jahres “hängen bleibt”, weil der 29. und der 30. ein Samstag bzw. Sonntag ist. Im Vorjahr waren diese Tage aber Werktage. Eine Variante ist daher, das YTD-Datum immer auf den kompletten Monat des letzten Bewegungsdatums auszuweiten:

    [YTD Date] := var maxDate = MAXX(ALL(FaktUmsatz);FaktUmsatz[Datum]) return date(year(maxDate); month(maxDate) + 1; 1)

    Praktisch berechne ich den ersten Tag des Folgemonats und ändere das Measure von “kleiner gleich” auf “kleiner” des YTD-Datum ab:

    Umsatz YTD = CALCULATE(sum(‚Umsätze'[Umsatz]); FILTER(all(DimDatum); DimDatum[Date] >= DATE(YEAR([YTD Date]);1;1) && DimDatum[Datum] < [YTD Date]))

    Oder aber man stellt sich auf den Standpunkt, dass man immer nur bis zum Ende des letzten Monats auswertet, weil der aktuelle immer als Anbruch gilt. Dann setze ich das Datum auf den ersten des Monats des letzten Bewegungsdatums:

    [YTD Date] := var maxDate = MAXX(ALL(FaktUmsatz);FaktUmsatz[Datum]) return date(year(maxDate); month(maxDate) ; 1)

    IsYTD in der Kalendertabelle

    Mit dem YTD-Datum kann man die gezeigten Measures berechnen. Die YTD-Information kann man auch in der Datumstabelle verwenden, so dass man beispielsweise Diagramme filtern kann. Dazu lege ich mir eine berechnete Spalte an und prüfe, ob das Datum der aktuellen Zeile im YTD-Range liegt:

    IsYTD = DimDatum[Datum] > date(year([YTD Date]); 1;1) && DimDatum[Datum] < [YTD Date]

    In Power BI verwende ich diese berechneten Spalten in der Datumstabelle häufiger, da man dort in jedem Visual den Filter unabhängig setzen kann. Damit kann man eine Measure für den aktuellen Umsatz, den Vorjahresumsatz, die Differenz absolut und relativ anlegen und am Ende verschiedenen Visuals zuordnen:

    • YTD – für die Jahresbetrachtung
    • QTD – für die Quartalsbetrachtung
    • MTD – für eine Monatsbetrachtung

    In Excel Power Pivot muss man leider jeweils eigene Measures erstellen.

    Unterstützung gefällig?

    Für einen noch schnelleren Start bieten wir Trainings und Workshops an. Im Training schulen wir Frontal dich und deine Kollegen, in unseren Workshops bauen wir Reports direkt gemeinsam mit dir auf.

    Für einen Einstieg in Excel Power Pivot schau bei unseren Online Kursen vorbei.

    Gerne kannst du uns direkt per E-Mail kontaktieren oder ruf einfach an!

    Kategorien: Artikel, Datum-Dimension, Power BI, Power Pivot, Tipps
    Holger Gubbels

    Holger Gubbels ist Spezialist für alles, was mit Business Intelligence zu tun hat. Der Diplom-Informatiker strukturiert und löst als Data Scientist und Berater der mogular GmbH tagtäglich komplexe Problemstellungen rund um die Datenanalyse. Als Dozent der Hochschule für Technik Stuttgart ist er ein ausgewiesener Experte seines Fachs und gewohnt, Inhalte einfach zu erläutern. Genau das macht er auch hier in seinen Beiträgen für das PowerPivotInsights Blog. Zusätzlich ist er Autor für die Computerwoche und den Springer-Verlag. Sie erreichen ihn unter hg@mogular.com

    Antwort schreiben

    Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.