Kategorie: PowerPivot

Kalendertabelle – volle 10 Jahre

Warum war ein Monat so umsatzschwach? Wie viele Verkaufstage hat ein Monat? Oder wie sieht mein aktueller Umsatz im Vergleich zum passenden Vorjahreszeitraum aus?

Zahlen sagen nur wenig aus, wenn man sie ohne Kontext betrachtet. Zeitraum, Zeitpunkt, parallel laufende Ereignisse etc.
Zudem sieht der Kalender auch nicht für jeden gleich aus.

Wir betrachten meist das Kalenderjahr – und das schon aus unterschiedlichem Blickwinkel. Für die einen beginnt die Woche am Montag und hat fünf Arbeitstage – für die andere jedoch 6 Verkaufs- und Arbeitstage. Für die einen beginnt die erste Kalenderwoche automatisch am 1. Januar einen Jahres (z. B. in den USA) – für uns in der Woche, in der mehr neue Tage als Tage des alten Jahres sind (also mindestens vier Januartage und somit auch der Donnerstag). Für das eine Unternehmen ist das Wirtschaftsjahr identisch mit dem Kalenderjahr – wenn man sich aber die Geschäftsberichte großer Unternehmen anschaut, beginnt zum Beispiel bei thyssenkrupp das Geschäftsjahr am 1. Oktober eines Jahres und endet am 30. September des Folgejahres. Entsprechend werden auch die Monate anders gezählt. Theater widerum haben neben Kalender- und Geschäftsjahr noch ein zusätzliches Spieljahr … ach ja, Ferien pro Bundesland und abweichende Feiertage gibt es ja auch noch.

Kalendertabellen selbst können Großteils automatisiert errechnet werden. Andere Informationen wie z. B. der Reformationstag aus dem Jahr 2018 und z. B. die Ferien eines Bundeslandes wie Nordrhein-Westfalen müssen manuell ergänzt werden.

In einer neuen Seminarreihe gehe ich auf die Kalendererstellung und Datumsberechnung in Excel selbst, in Power Query mit der eigenen Programmiersprache M, sowie der Erstellung in PowerPivot mit DAX-Funktionen (Data Analysis Expressions) ein. Als i-Tüpfelchen könnte noch die Kalenderberechnung in VBA folgen. Oder schreibt man das mittlerweile iTüpfelchen?

Einige sind ja schon auf mein PDF-Skript gestoßen. Das wird nach und nach fortgeführt: Excel – Datums- und Zeitfunktionen

Auch hatte ich an anderer Stelle schon mal eine Kalendertabelle zur Verfügung gestellt und die Feiertage und Schulferien von NRW ergänzt. Diese Informationen und weitere Ergänzungen gibt es nun zum Download in folgender Excel-Datei (aktualisiert!): Kalendertabelle

Die Datei liegt im XLSX-Format vor und beinhaltet auch Informationen zur Ermittlung von Geschäfts- bzw. Fiskaljahren.

Für alle, die nichts herunterladen wollen oder dürfen, die Spalten enthalten:

  1.  Spalte:   Datum –> Liste manuell gelistet
  2.  Spalte:   Jahr =JAHR([@Datum])
  3.  Spalte:   Monat_kurz =MONAT([@Datum])
  4.  Spalte:   Monat =TEXT([@Datum];“MMMM“)
  5.  Spalte:   Tag Monat =TAG([@Datum])
  6.  Spalte:   Tag Woche =WOCHENTAG([@Datum];2)
  7.  Spalte:   Wochentag =TEXT([@Datum];“TTTT“)
  8.  Spalte:   Tage im Monat =TAG(MONATSENDE(DATUM([@Jahr];[@[Monat_kurz]];1);0))
  9.  Spalte:   KW =KALENDERWOCHE([@Datum];21)
  10.  Spalte:   Quartal =“Q“&AUFRUNDEN([@[Monat_kurz]]/3;0)
  11.  Spalte:   Jahr Quartal =[@Jahr]&“ „&[@Quartal]
  12.  Spalte:   Jahr Monat =TEXT([@Datum];“JJJJ-MM“)
  13.  Spalte:   Halbjahr =WENN([@[Monat_kurz]]<=6;“HJ 1″;“HJ 2″)
  14.  Spalte:   Jahr KW =JAHR([@Datum])-WENN(UND(MONAT([@Datum])=1;
    KALENDERWOCHE([@Datum];21)>51);1;0)+WENN(UND(MONAT([@Datum])=12;
    KALENDERWOCHE([@Datum];21)=1);1;0)&“-„&TEXT(KALENDERWOCHE([@Datum];21);“00“)
  15.  Spalte:   Feiern NRW –> manuell auf WAHR und FALSCH gesetzt
  16.  Spalte:   Feiertage NRW –> manuell auf WAHR und FALSCH gesetzt
  17.  Spalte:   Werktag =WENN([@[Tag Woche]]<=5;“Werktag“;“Wochenende“)
  18.  Spalte:   Verkaufstage Monat NRW =ZÄHLENWENNS([Tag Woche];“<=“&6;[Jahr];JAHR([Datum]);
    [Monat_kurz];MONAT([Datum]);[Feiertag NRW];FALSCH)
  19.  Spalte:   Werktage Monat NRW =ZÄHLENWENNS([Tag Woche];“<=“&5;[Jahr];
    JAHR([Datum]);[Monat_kurz];MONAT([Datum]);[Feiertag NRW];FALSCH)
  20.  Spalte:   FY =rngFYPräfix&WENN(rngStartFiskaljahr=1;[@Jahr];
    WENN([@[Monat_kurz]]<rngStartFiskaljahr;[@Jahr]-1&“/“&RECHTS([@Jahr];2);
    [@Jahr]&“/“&1*RECHTS([@Jahr];2)+1))
  21.  Spalte:   FY M =[@FY]&“ – „&TEXT(WENN([@[Monat_kurz]]<
    rngStartFiskaljahr;12-rngStartFiskaljahr+[@[Monat_kurz]];[@[Monat_kurz]]-rngStartFiskaljahr)+1;“00“)
  22.   Spalte:   FY Q =[@FY]&“ – Q“&AUFRUNDEN((WENN([@[Monat_kurz]]<
    rngStartFiskaljahr;12-rngStartFiskaljahr+[@[Monat_kurz]];
    [@[Monat_kurz]]-rngStartFiskaljahr)+1)/3;0)

Auf dem zweiten Tabellenblatt gibt es zwei Zellen benannte Zellen. Die mit rngStartFiskaljahr benannte Zelle muss auf Werte zwischen 1 und 12 gesetzt werden. Die benannte Zelle rngFYPräfix beinhaltet z. B. „GJ “ für Geschäftsjahr oder „FY “ für fiscal year.

Alle Angaben und Berechnungen sind ohne Gewähr. Also bitte noch einmal prüfen. Vielleicht mag sich auch jemand die Mühe machen und die Feiertage und Ferien für andere Bundesländer eintragen – dies allein war schon irre aufwendig.

So!!! Nun haben wir eine Basis, auf die ich in neuen Videos eingehen werde.
Freue mich auf Feedback bei YouTube und auch Danke an alle, die den Kanal supporten.

Bis bald
Andreas

Excel – Power Pivot – HasOneValue und Values

In meinen Daten befinden sich Werte, die entweder in Euro EUR oder in US-Dollar USD gemeldet wurden. Mit berechneten Elementen der regulären PivotTables hätte ich dies in Pivot umrechnen können. Natürlich ginge auch eine Hilfsspalte neben den Daten.

Die Werte sollen addiert werden können.

540a

In dieser Tabelle ist aufgeführt, welches Land in welcher Währung gemeldet hat.

540b

Und natürlich gibt es für spätere Auswertungen noch eine Kalendertabelle.

540c

Alle drei Tabellen werden ins Power Pivot Data Model geladen. Hier die Diagrammsicht.

540d

In der Faktentabelle Umsatz wird nun ein berechnetes Feld mit dem Namen Betrag errechnet. Betrag rechnet hier aber Äpfel und Birnen zusammen – vielmehr Euro und Dollar. Der Betrag in Höhe von 162 Millionen ist zu hoch. Bereinigt müssten eigentlich rund 153 Mio. resultieren.

540e

Betrag:=
SUM(Umsatz[Wert])/
IF(HASONEVALUE(Countries[Gruppe]);
IF(VALUES(Countries[Gruppe])="USD";1,36;1)
;1)

Es wird die Summe der Umsätze gebildet. Dabei wird aber geschaut, ob einer der Datensätze der Gruppe USD zugeordnet werden kann. Falls ja, wird durch den EUR-USD-Wechselkurs 1,36 geteilt. Alternativ wird der Wert 1 genommen. Für den Fall, dass es das Feld Countries[Gruppe] nicht gibt, wird auch der Wert 1 genommen.

Da sich das Feld Land in der Dimensionstabelle mit der Währungszuordnung befindet, kann dieses Feld Land genommen werden. Die Zuordnung zu einer Währung erfolgt hier automatisch.
Kopfrechenaufgabe: Hier müssten die korrekten 153 Mio. resultieren.

540f

Wird hingegen Land aus der Faktentabelle genommen, ist die zusätzliche Gruppierung nach Währungsgruppe notwendig.
Die Summe liegt bei 153 Mio. Euro.

540k

Ohne diese Gruppe wird nicht korrekt umgerechnet. Die Summe beträgt rund 162 Mio. Einheiten aus EUR und USD gemischt.

540j

Hier geht es zum Verzeichnis mit den Übungsdateien:
http://1drv.ms/TRQlgF

Hier geht es zum Video:

Videolink: http://youtu.be/xH9ETKA3UYY

Excel – Power Query – Monatsanfang und Monatsende

Die Funktionen Date.StartOfMonth und Date.EndOfMonth ermitteln passend zu einer Datumsspalte den jeweiligen Monatsanfang und das Monatsende.

Wichtig bei diesen Funktionen ist die korrekte Groß-/Kleinschreibung.
Befinden sich in der Spalte namens „Datum“ z.B. Verkaufsdaten, kann mit Date.EndOfMonth([Datum]) der jeweilige Monatsende errechnet werden.

Natürlich stehen mir Monatsanfang und Monatsende auch über eine vernünftige Kalendertabelle in PowerPivot zur Verfügung. Aber wer weiß schon, wofür diese Spalten benötigt werden können?

Hier gibt es die Übungsdatei:
Excel Power Query 2-1

Hier geht es zum Video:

Videolink: http://youtu.be/nXVa5O7Y9oI