PQ: Ganze Monate und prozentualen Anteil von Teil-Monaten berechnen

  Wissensstand: Level 3 ⇒  Excel GUT!, mindestens Basis-Kenntnisse in Power Query   

Berechnung der Differenz zweier Datumsangaben in ganzen Monaten sowie prozentualer Teil eines Monats 

In einem Forum tauchte die Frage auf, wie die Zeitdifferenz in ganzen Monaten zwischen zwei gegebenen kalendarischen Daten berechnet werden kann. Als Besonderheit kam nach einigen “Missverständnissen“ noch die Forderung, dass der erste Monat nur dann voll berechnet werden soll, wenn der Start auch am Ersten des Monats liegt. Im anderen Fall soll dieser Monat prozentual (anteilig) von den gesamt-Tagen des Monats berechnet werden. Der letzte Monat des gegebenen Zeitraums wird in jedem Fall komplett berechnet.  

In diesem Foren-Thread wurden verschiedene Lösungen angeboten, die vielfach auf der Funktion DATEDIF (Formel) oder Datediff (VBA) basierten. Der Fragesteller fand aber stets irgendwelche Konstellationen, die mit den Lösungsvorschlägen zu falschen Ergebnissen führten. Aus unterschiedlichen Gründen habe ich mich an der Diskussion nicht beteiligt; in erster Linie lag es gewiss daran, dass ich „Salamitaktik“ mit nachträglichen Änderungen der eigentlichen Fragestellung nicht mag bzw. zig Nachbesserungen der Fragestellung erfolgen, um die eigentliche Frage zu verdeutlichen aber auch daran, dass in rascher Folge von den Helfern Lösungen präsentiert worden sind.

Ich habe eine Lösung mit Power Query gefunden, die gewiss für jeden Fall, in jeder Konstellation von Start- und Enddatum zu einem korrekten Ergebnis führt. Eine Prüfung verschiedener Beispiele, die im Forum zu fehlerhaften Ergebnissen geführt haben ergab, dass PQ das korrekte Ergebnis liefert. 😎 

Eine Besonderheit dieser Lösung liegt darin, dass die Eingabe-Tabelle nur eine einzige Zeile hat. Es wird also nur ein einziger Zeitraum berechnet. Es wäre zwar auch möglich, mehrzeilige Tabellen auszuwerten, aber der Aufwand würde aus meiner Sicht unverhältnismäßig höher sein. Außerdem war es auch nicht in dieser Form gefragt. 😉 Und ich habe auch keine Kontrolle eingebaut, ob das Ende-Datum ≥ dem Startdatum ist.

Nach dem Einlesen dieser Tabelle in den Power Query Editor ändere ich zu Beginn den Datentyp beider Spalten von Datum/Uhrzeit auf (nur) Datum. Das hat nicht nur kosmetische Gründe; PQ kann mitunter mit Datum-Werten (statt Datum/Zeit) komplikationsloser arbeiten.

Auch wenn mit reinem M-Code manches „schlanker“ darstellbar wäre, gehe ich den Weg über Hilfsspalten, die ich ja später in Power Query wieder löschen kann. Transparenz geht aus meiner Sicht nun einmal vor. Da ja der erste Monat nur dann mit einbezogen werden soll, wenn der Start auf dem Monatsersten fällt, erstelle ich eine Spalte mit der Überschrift Start, wo ich den zu berechnenden Start-Tag eintrage:

  • Menü Spalte hinzufügen | Benutzerdefinierte Spalte
  • Neuer Spaltenname: Start
  • Benutzerdefinierte Spaltenformel:
    =if Date.Day([Erster Tag]) = 1 then [Erster Tag]
    else Date.AddMonths(Date.StartOfMonth([Erster Tag]), 1)
  • Bei meiner Vorgehensweise der Lösungsfindung ist es eigentlich nicht erforderlich, den Ultimo des letzten Monats zu berechnen. Ich mache das hier einfach einmal, um Ihnen auch diese Funktion der Sprache M aufzuzeigen. Die Überschrift in der neuen Benutzerdefinierten Spalte wird naturgemäß Ende sein und die Formel:
    =Date.EndOfMonth([Letzter Tag])

Prinzipiell wäre es in diesem Stadium möglich, in plain Excel oder in VBA die Funktion DATEDIF/Datediff zu verwenden. Aber wenn wir schon einmal in Power Query sind, sollte das Ergebnis auch auf dieser Ebene berechnet werden. Meine erste Idee war, per Formel die Anzahl der Differenz-Tage zu berechnen, das Ergebnis durch 30 dividieren und dann runden. Das wird in den meisten Fällen auch zu einem korrekten Ergebnis führen. Aber in den meisten Fällen hat ja auch die von den anderen Helfern vorgeschlagene Formel in plain Excel oder VBA hingehauen, nur in Ausnahmefällen nicht. Und aus dem Grunde finde ich den hiervor aufgezeigten Lösungsweg nicht ganz passend, denn es kann nicht ausgeschlossen werden, dass der Zeitraum einmal sehr viele Jahre umfasst und dadurch die 30-Tage-Berechnung zu Fehlergebnissen führen kann. Dennoch will ich Ihnn nichts verheimlichen: Die Formel für solch eine Berechnung würde dann so aussehen:
=Number.Round(Number.From([Ende]-[Start])/30)

Zugegeben, es gibt noch weitere Methoden mit einem kleinen Programm in der Sprache M die Berechnung sauber durchzuführen. Aber ich wähle da den etwas pragmatischeren Weg und arbeite mit einer Hilfsspalte, wo ich jeden Tag des benannten Zeitraums einzeln aufliste. Und daraus generiere ich dann in einer neuen Spalte für jede Zeile einen Textwert, der dem Format MM-JJJJ entspricht. Anschließend berechne ich daraus die Anzahl der Unikate und das Ergebnis ist die korrekte Anzahl der Monate. Hier Schritt für Schritt eine kurze Anleitung:

  • Spalte hinzufügen | Benutzerdefinierte Spalte
  • Als neuen Spaltennamen gebe ich beispielsweise Anzahl Monate ein.
  • Die Benutzerdefinierte Spaltenformel sieht so aus:
    =List.Dates([Start], Number.From([Ende]-[Start]) +1, #duration(1,0,0,0))

Die neu erstellte Spalte „Anzahl Monate“ (hier noch mit der Spalte „Ende“)

  • Erweitern Sie nun die Überschrift der Spalte Anzahl Monate durch einen Klick auf den Doppelpfeil Doppelpfeil
  • Wählen Sie im Dropdown die obere Möglichkeit: Auf neue Zeilen ausweiten
  • Per Kontrolle werden sie nun erkennen, dass die Tabelle im Power Query-Editor für jeden Tag des Zeitraums ab dem ersten vollen Monat bis hin zum letzten Tag des letzten Monats einen fortlaufenden Datum-Eintrag enthält.
  • Start/Home | Verwalten | Verweis, um ein Verweis-Duplikat der Abfrage zu erstellen.
  • Geben Sie der neuen Abfrage den Namen volle Monate.
  • Zur Erstellung der Textspalte im (Excel-) Format MM-JJJJ wählen Sie im Menüband wiederum Benutzerdefinierte Spalte, Spaltenname könnte auch MM-JJJJ sein und geben dann diese Formel exakt so ein:
    =Date.ToText([Anzahl Monate], "MM-yyyy")
  • Da Sie ja nur das Ergebnis der einmaligen Einträge in dieser Spalte brauchen, ein Rechtsklick in die Überschrift und wählen Sie im Kontextmenü Duplikate entfernen. Das Ergebnis wird sich so darstellen:

Die gefilterte Liste, nur Einzelwerte der Monate

  • Die Spalte bleibt markiert. Wählen Sie im Menü Transformieren das Symbol Statistiken | Eindeutige Werte zählen.
  • Das Ergebnis ist vielleicht etwas überraschend … 😯 

Die Liste muss noch zu einer Tabelle konvertiert werden

  • Klicken Sie auf Zu Tabelle, um die Liste zu einer auswertbaren Tabelle umzuformen.
  • Klicken Sie hier auf das ganz linke Symbol Zu Tabelle und Power Query erstellt eine Tabelle mit einer Spalte, Überschrift Column1 und in der einzigen Zeile steht die berechnete Anzahl der Monate: 14.

▲ nach oben …

Noch offen ist die Berechnung des Anteils im ersten Monat für den Fall, dass der reale Start-Tag nicht der Monatserste ist sondern irgendwann im Laufe des Monats liegt. Da sollen dann ja nur jene Tage prozentual berechnet werden, die auch im Start-Monat liegen. Dabei ist zu berücksichtigen, dass eine wirklich taggenaue Berechnung vorgenommen werden soll. Erstellen Sie also dieser neuen Mini-Tabelle eine neue Spalte und vergeben für die exakte Berechnung der Monate eine beliebige Überschrift; ich verwende Monate (exakt). Hier trage ich nun folgende Formel ein:
=14+2/31

Erstaunt? Ja, das dürfen Sie auch sein. Klar, die Berechnung ist mathematisch korrekt, aber formal natürlich Unsinn. Denn ich habe die durch Augenschein am Bildschirm gelesenen Werte von Hand in die Formel eingegeben. Und das kann es ja nun nicht wirklich sein, denn wenn die Ursprungsdaten nicht 14 Monate umfassen, 2 Rest-Tage in einem Monat mit 31 Tagen haben, dann stimmt das Ergebnis natürlich nicht. 🙁 

Okay, der Weg dahin ist im „wirklichen Power Query-Leben“ doch etwas komplexer. Ich werde Ihnen hier einmal (ausnahmsweise im non-$$-Bereich) den Weg sehr detailliert, einschließlich der Zwischenschritte vorstellen. (Sie dürfen sich auch gerne bedanken, siehe ganz am Ende des Beitrages).

Im ersten Schritt werden Sie sich in dieser Mini-Abfrage das Datum des Start-Tages per Power Query holen. Es gibt mehrere Möglichkeiten; die offensichtlichste ist gewiss, dass dieser Wert In der Tabelle Auswertungszeitraum in der Spalte Start steht. Aber so wie in plain Excel, mit einem = und dann ein Klick in die Ziel-Zelle geht das hier nicht. Sie können den Wert aber in einer Benutzerdefinierten Spalte abfragen:
=Table.First(Auswertungszeitraum, "Start")

Der Klarheit wegen gebe ich dieser Spalte vorerst einmal die Überschrift Teil-Monat%, denn der verbleibende Teil des Monats soll ja als Prozent-Wert berechnet werden. Das Ergebnis wird sich dann im ersten Schritt so darstellen:

Das Ergebnis der Funktion ist ein Record (Datensatz)…

Es wird Sie gewiss nicht überraschen, dass Sie im nächsten Schritt die Überschrift Teil-Monat% durch einen Klick auf den Doppelpfeil Doppelpfeil  erweitern werden. Entfernen Sie im Dialog alle Häkchen (einschließlich Ursprüngliche Spaltennamen…) und setzen das Häkchen bei Erster Tag, wo ja der erste Tag des Zeitraums (also auch des ersten Monats) vermerkt ist:

Nur dieses eine Kästchen bleibt markiert

Nach der Bestätigung durch OK wird die Spalte automatisch zu Erster Tag umbenannt (also dem Namen der ursprünglichen Spalte) und der Inhalt ist auch jener Wert, den Sie gewiss erwartet haben: 30.01.2018. Damit ist die Grundlage gelegt, um die (in diesem Fall)  die restlichen 2 von 31 Tagen des Monats zu berechnen. Natürlich geht das auch mit einer Benutzerdefinierten Spalte, welcher ich den Namen % vom Monat gebe:
=(Number.From(Date.EndOfMonth([Erster Tag]) - [Erster Tag]) +1) / Date.Day(Date.EndOfMonth([Erster Tag]))

Die Umwandlung durch die Funktion Number.From ist erforderlich, da eine Addition zu Werten im Datum-Format zu einem Fehler führt. Die Sprache M (also Power Query) ist in Sachen Datentyp recht penibel. – Wenn ich den Wert der „aktiven“ Tage durch die Anzahl der Tage im Monat (Date.Day(Date.EndOfMonth) dividiere, dann habe ich den prozentualen Wert des Anteils. Das Ergebnis entspricht 2/31, also (als „normale“ Dezimalzahl ausgedrückt) 0,064516129032258063.

Im letzten Schritt markieren Sie die beiden Spalten volle Monate und % vom Monat, Spalte hinzufügen | Statistiken | Summe und die neue, umgeformte Spalte Addition enthält das Ergebnis. – Und wie auch im Forum-Thread kommt zum Schluss die Erklärung, wozu diese Zahl gebraucht wird: Für einen Vertrag wird basierend auf der Monats-Laufzeit ein zu berechnender Betrag berechnet. Der erste Monat wird  anteilig berechnet, der letzte Monat wird stets voll in Anrechnung gebracht.

Das gewünschte Ergebnis ist erreicht. Sie können nun die Überschrift Ihren Wünschen anpassen, die anderen Spalten löschen und über Schließen & laden oder Schließen & laden in… das Ergebnis in ein Excel Arbeitsblatt schreiben. – Zugegeben, beim ersten Mal ist es eine ganze Menge Arbeit. Aber wegen der absoluten Genauigkeit ist der Aufwand gerechtfertigt. Zumal Sie mit einem einzigen Mausklick auf Aktualisieren bei geänderten Quelldaten sofort ein angepasstes Ergebnis haben.  

▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag (2,00€ bis 4,00€) Ihrerseits freuen …

Dieser Beitrag wurde unter Datentyp anpassen, Datum & Zeit, Foren-Q&A, Power Query, PQ-Formeln (Sprache M), Spende/Donation abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.