Im letzten Artikel haben wir besprochen, wozu man diese Calculate() benötigt. Dort haben wir einen einfachen Periodenvergleich auf ein fest eingegebenes Jahr vorgenommen. Schöner wäre es, könnten wir dynamisch immer auf das vorherige Jahr gehen – das geht in Kombination mit Filter().

Umsatzvergleich im Vertrieb

In diesem Artikel greifen wir wieder auf die Daten der AdventureWorks-Datenbank zurück. Wir vergleichen, wie sich der Verkaufsumsatz bei den Verkäufern verglichen mit dem Vorjahr entwickelt hat. Unser Dashboard soll folgendermaßen aussehen: Dashboard

Das Datenmodell

Datenmodell Aus der Tabelle SalesOrderHeader erhalten wir alle Aufträge sowie den Nettobetrag (SubTotal). Wir verwenden als Datum das ShippingDate. Der jeweilige Verkäufer ist in der Tabelle SalesPerson hinterlegt, die via SalesPersonId verknüpft ist. Die Tabelle Person benötigen wir, da wir nicht die Nummer des Verkäufers anzeigen möchten, sondern den Namen. Allerdings verwenden wir Person nicht als Dimension, sondern legen auf SalesPerson eine berechnete Spalte mit dem Namen Lastname an. Die Formel lautet einfach:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Das nur deswegen, weil wir im Slicer später nicht alle Personen haben möchten, sondern nur die Verkäufer.

Die Kennzahlen

Die Berechnung des aktuellen Umsatzes ist einfach – allerdings haben wir uns trotzdem für ein berechnetes Feld entschieden, damit wir im Falle von “keinem Umsatz” eine “0” als Wert bekommen (an Stelle von “nichts”):

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Die Funktion für die Berechnung des Umsatzes aus dem letzten Artikel sah wie folgt aus:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Dieser bringt uns hier nicht weiter – wir benötigen eher so etwas:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Das führt aber zu einem Fehler. Einfache Filter in der Calculate-Funktion müssen immer die Form Spaltenname = haben. Für unseren Fall benötigen wir die Funktion Filter(), die wir im Artikel über Filter besprochen haben. Unser erster Versuch wäre dann:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Das kann nicht funktionieren, da der Filter den Benutzerfilter einschränkt. Hat der Benutzer bspw. das Jahr 2007 gewählt, gibt dieser Filter alle Zeilen zurück, die im Jahr 2006 das Jahr 2007 haben – davon kann es keine Zeilen geben. Wir müssen den Benutzerkontext umgehen. Wie? Genau, mit dem All()-Filter:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Dieser Ausdruck gibt immer noch keine Werte zurück. Calendar[Year] = Calendar[Year] – 1 vergleicht potentiell viele Tabellenzeilen. Hat der Benutzer 2007, 2008 und 2009 gewählt, weiß Power Pivot nicht, wie er hier agieren soll. Meines Erachtens müsste dieser Ausdruck einen Fehler werfen – tut er leider nicht. Dem Umstand mit mehreren gewählten Jahren ist leicht beizukommen – wir nehmen einfach das kleinste ausgewählte Jahr und ziehen davon 1 ab:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Die Krux mit dem Kontext…

Auch dieser Ausdruck gibt keinen Wert zurück. Zwar gibt der Filter alle Zeilen aus dem Vorjahr zurück – Calculate nimmt aber seine Filter und schränkt damit den aktuellen Benutzerkontext ein. Und der Benutzerkontext steht beispielsweise auf 2007 – der Filter gibt 2006 zurück. Die Schnittmenge ist natürlich leer… Daher müssen wir die Formel wie folgt verändern:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Mit dem einem All()-Filter auf Calendar heben wir den Filter auf Calendar auf – der Filter auf einen gewählten Verkäufer bleibt hingegen bestehen! Mit der Schnittmenge auf alle Aufträge des Verkäufers und der Einschränkung auf das Vorjahr haben wir jetzt tatsächlich den Umsatz des Vorjahres: Umsatz Vorperiode im Test Na ja, zumindest fast. Wir haben die obige Formel in die Kennzahl UmsatzVorjahrTest gepackt, um die obige Formel gegen die “richtige” zu testen. UmsatzVorjahrTest gibt leider immer die Summe des Vorjahres zurück. Der Monat wird nicht berücksichtigt.

All() mit Spalten

Die Begründung dafür ist ganz leicht: Wir haben in der Formel den Filter auf das Datum komplett zurückgesetzt, also auch die Monate. Eigentlich wollen wir nur das Jahr zurücksetzen. Daher bietet ALL() auch die Möglichkeit einzelne Spalten aus dem Filter herauszunehmen:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Dann klappt's auch mit den Monaten: Korrekte Darstellung mit ALL()

Time Intelligence-Funktionen

Es geht auch einfacher:

  • US
    Für Power-BI oder englisches Excel
    formatieren (Komma statt Semikolon).
    Mehr Infos
  • DE
    Für deutsches Excel formatieren
    (Semikolon statt Komma).
    Mehr Infos

Mit Funktionen wie SAMEPERIODLASTYEAR() stellt Power Pivot verschiedene Funktionen zur Verfügung, die intern das Gleiche machen, wie die oben beschriebene Formel. Von diesen Funktionen gibt es viele mehr, auf die wir in zukünftigen Artikeln näher eingehen werden. Wichtig zu verstehen ist aber, dass man sich diese Formeln selbst basteln kann – das ist gerade dann wichtig, wenn man sich nicht auf einen “normalen” Kalender beziehen möchte.