relativen Datumsfilter setzen

Nachdem ich in den letzten Beiträgen vor allem über das Erstellen eines dynamischen Kalenders geschrieben habe, beleuchte ich diesmal ein ganz anderes Thema im Zusammenhang mit der Datumsdimension.
Wir können in Power BI Filter (Slicers) setzen und so definieren, welche Daten angezeigt werden sollen. In der aktuellen Version (per 30. Mai 2017) ist es auch möglich zu definieren, aus welchem Zeitraum (von bis, nach, vor) die Daten stammen sollen. Was fehlt ist die Möglichkeit, einen relativen Datumsfilter anzuwenden. Dazu müssen wir auf eine andere Technik zurückgreifen.

Definition des relativen Datumsfilter

Der relative Datumsfilter setzt das gewünschte Datum in Kontext zum Beispiel zum aktuellen Tag, Monat, Jahr usw. Relativ in dem Sinne, dass sich mit der Änderung des Ursprungsdatum der gesuchte Zeitraum verändert. Suche ich per heute 30. Mai 2017 alle Daten der letzten sechs Monate, erhalte ich entsprechend einen Report der Monate Dezember 2016 bis Mai 2017.

Erstellen des relativen Datumsfilter

Angenommen Ihr möchtet in Euren Reports jeweils einen Filter zur Verfügung stellen, der dem Benutzer die Möglichkeit zum Filtern folgender relativer Datumsbereiche ermöglicht:

  • laufender Monat
  • letzter Monat
  • letzte 2-3 Monate
  • letzte 4-6 Monate
  • letzte 7-9 Monate
  • letzte 10-12 Monate
  • 1 Jahr und mehr zurück
  • zukünftig

Was Ihr dazu braucht sind zwei neue Spalten in der Datumsdimension (Kalender), die genau diese Berechnung vornehmen.

Nehmt als Grundlage die Datumsdimension aus dem letzten Beitrag und ergänzt sie mit einer neuen Spalte “relativer Monat”. Dieser berechnet die Differenz zwischen zwei Daten in Monaten. Als Code gebt Ihr ein:

(Date.Year([Datum])-1)*12+Date.Month([Datum])-((Date.Year(DateTime.LocalNow())-1)*12+
Date.Month(DateTime.LocalNow()))
)

Die Formel extrahiert das Jahr der Datumspalte (z.B. 1.12.2016 = 2016), subtrahiert 1 und multipliziert das Resultat mit 12 und zählt die Nummer des Monats dazu (hier: 12).
Das gibt also 2016-1 = 2015 * 12 + 12 = 24192.

Das selbe wird für den laufenden Monat berechnet (heute ist der 28.5.2017). Dies ergibt 24197. Die Differenz davon ist der relative Monat => –5. Eine negative Zahl kennzeichnet vergangene Daten.

Ergänzt die Tabelle um eine weitere Spalte namens “Datumsfilter”.
Als Code verwendet Ihr:

if [relativer Monat] > 0 then „zukünftig“ else
if [relativer Monat] = 0 then „laufender Monat“ else
if [relativer Monat] >= -1 then „letzter Monat“ else
if [relativer Monat] >= -3 then „letzte 1-3 Monate“ else
if [relativer Monat] >= -6 then „letzte 4-6 Monate“ else
if [relativer Monat] >= -9 then „letzte 7-9 Monate“ else
if [relativer Monat] >= -12 then „letzte 10-12 Monate“ else „1 Jahr und länger zurück“ )

Anwendung des relativen Datumsfilter

Der Code sollte selbst erklärend sein. Als Resultat haben wir nun eine Spalte, die wir als Filter einsetzen können. Kehrt zurück in die Benutzeroberfläche des Power BI Desktops und zieht das Feld “Datumsfilter” in eine Reportseite. Wählt danach das Filtersymbol und erstellt so einen Filter. Und schon habt Ihr in kurzer Zeit einen relativen Datumsfilter erstellt.

relativer Datumsfilter

 

Mehr Artikel über Power Query findet Ihr im Power Query Online Buch.

Happy Querying!

Tabellen zusammenführen mit relativer Bedingung

Tabellen in Power Query und Power BI Desktop zusammenzuführen oder anzufügen ist über die Benutzeroberfläche einfach zu realisieren. Dies gilt jedoch nicht, falls relative Bedingungen wie „gültig von bis“ im Spiel sind.
Im heutigen Beispiel soll zur Sales Tabelle die Bruttomarge aus einer andern Tabelle hinzugefügt werden. Die Bruttomarge ist jedoch nicht starr und ändert sich von Zeit zu Zeit.

„Tabellen zusammenführen mit relativer Bedingung“ weiterlesen

SVerweis mit Power Query

Wer kennt ihn nicht, den SVerweis? Viele verzweifeln an ihm, andere setzen ihn virtuos ein. Aber der SVerweis hat einige Nachteile. In diesem Beitrag zeige ich Euch, wie ihr den SVerweis mit Power Query ganz einfach nachbildet und die Limitationen des SVerweises beseitigen könnt.

„SVerweis mit Power Query“ weiterlesen

Dynamischer Power BI Kalender – Feiertagsliste

Im ersten Teil haben wir einen dynamischen Kalender aufgrund einer Faktentabelle erstellt. Diesem Kalender fehlen noch die Feier- und Arbeitstage. In diesem Beitrag zeige ich, wie die Feiertagsliste einfach erstellt werden kann. „Dynamischer Power BI Kalender – Feiertagsliste“ weiterlesen

Dynamischer Kalender in Power BI

Jedes Datenmodell benötigt eine Zeitdimension in Form eines Kalenders. Aber nicht immer steht ein solcher Kalender out-of-the-box zur Verfügung. Damit manuelle Arbeiten wegfallen, zeige ich Euch, wie ein dynamischer Kalender in Power BI oder Power Query für Excel einfach erstellen werden kann. „Dynamischer Kalender in Power BI“ weiterlesen