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

Dynamischer Power BI Kalender – Arbeitstage

Im ersten Teil haben wir einen dynamischen Kalender aufgrund einer Faktentabelle erstellt. Im zweiten Teil haben wir zur Ermittlung der Feiertage eine kleine Funktion geschrieben. Nun fügen wir die Feiertagsliste in den Kalender ein und erstellen eine Spalte für die Arbeitstage. „Dynamischer Power BI Kalender – Arbeitstage“ weiterlesen

Wiederverwenden von Queries in Power BI

Es ist uns sicher allen schon mal passiert, dass wir eine Query erstellen wollten und  gedacht haben: “Das habe ich doch schon mal gemacht”.
Aber wo ist die entsprechende Query? In welcher Lösung habe ich diese verwendet? Der Beitrag zeigt, wie das Wiederverwenden von Queries in Power BI und Power Query auf einfache Art und Weise erfolgen kann.

„Wiederverwenden von Queries in Power BI“ weiterlesen

Reuse your queries in Power BI

Probably every one of us has encountered a situation, where he had to solve a problem he had already solved once. But where is the query or the PBI solution with this query??
Wouldn’t it be great to find a way to reuse your queries in Power BI Desktop or Excel?

„Reuse your queries in Power BI“ weiterlesen

Was ist Power Query?

Was ist Power Query?

Was ist Power Query? Um es kurz zu machen:
Power Query ist das beste Tool, das Microsoft in den letzten Jahren für den Import von Daten in Excel und bald auch in SQL erfunden hat!

Wer seine wertvolle Zeit in Excel sinnvoll einsetzen will, für den ist Power Query ein absolutes Muss. Mühsames Zusammenfügen von verschiedenen Exceldateien in eine Tabelle ist damit endlich vorbei. Einmal das Skript aufsetzen und immer wieder verwenden. Und so ist endlich ein grosser Schwachpunkt von Excel behoben: der einfache Datenimport aus Fremdsystemen. „Was ist 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