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

In einem Forum tauchte die Frage auf, wie die Zeit­d­if­ferenz in ganzen Monat­en zwis­chen zwei gegebe­nen kalen­darischen Dat­en berech­net wer­den kann. Als Beson­der­heit kam nach eini­gen “Missver­ständ­nis­sen” noch die Forderung, dass der erste Monat nur dann voll berech­net wer­den soll, wenn der Start auch am Ersten des Monats liegt. Im anderen Fall soll dieser Monat prozen­tu­al (anteilig) von den gesamt-Tagen des Monats berech­net wer­den. Der let­zte Monat des gegebe­nen Zeitraums wird in jedem Fall kom­plett berech­net.  

In diesem Foren-Thread wur­den ver­schiedene Lösun­gen ange­boten, die vielfach auf der Funk­tion DATEDIF (Formel) oder Date­d­iff (VBA) basierten. Der Fragesteller fand aber stets irgendwelche Kon­stel­la­tio­nen, die mit den Lösungsvorschlä­gen zu falschen Ergeb­nis­sen führten. Aus unter­schiedlichen Grün­den habe ich mich an der Diskus­sion nicht beteiligt; in erster Lin­ie lag es gewiss daran, dass ich „Salami­tak­tik” mit nachträglichen Änderun­gen der eigentlichen Fragestel­lung nicht mag bzw. zig Nachbesserun­gen der Fragestel­lung erfol­gen, um die eigentliche Frage zu verdeut­lichen aber auch daran, dass in rasch­er Folge von den Helfern Lösun­gen präsen­tiert wor­den sind.

Ich habe eine Lösung mit Pow­er Query gefun­den, die gewiss für jeden Fall, in jed­er Kon­stel­la­tion von Start- und End­da­tum zu einem kor­rek­ten Ergeb­nis führt. Eine Prü­fung ver­schieden­er Beispiele, die im Forum zu fehler­haften Ergeb­nis­sen geführt haben ergab, dass PQ das kor­rek­te Ergeb­nis liefert. 😎 

Eine Beson­der­heit dieser Lösung liegt darin, dass die Eingabe-Tabelle nur eine einzige Zeile hat. Es wird also nur ein einziger Zeitraum berech­net. Es wäre zwar auch möglich, mehrzeilige Tabellen auszuw­erten, aber der Aufwand würde aus mein­er Sicht unver­hält­nis­mäßig höher sein. Außer­dem war es auch nicht in dieser Form gefragt. 😉 Und ich habe auch keine Kon­trolle einge­baut, ob das Ende-Datum ≥ dem Start­da­tum ist.

Nach dem Ein­le­sen dieser Tabelle in den Pow­er Query Edi­tor ändere ich zu Beginn den Daten­typ bei­der Spal­ten von Datum/Uhrzeit auf (nur) Datum. Das hat nicht nur kos­metis­che Gründe; PQ kann mitunter mit Datum-Werten (statt Datum/Zeit) kom­p­lika­tion­slos­er arbeit­en.

Auch wenn mit reinem M-Code manch­es „schlanker” darstell­bar wäre, gehe ich den Weg über Hil­f­ss­pal­ten, die ich ja später in Pow­er Query wieder löschen kann. Trans­parenz geht aus mein­er Sicht nun ein­mal vor. Da ja der erste Monat nur dann mit ein­be­zo­gen wer­den soll, wenn der Start auf dem Monat­ser­sten fällt, erstelle ich eine Spalte mit der Über­schrift Start, wo ich den zu berech­nen­den Start-Tag ein­trage:

• Menü Spalte hinzufü­gen | Benutzerdefinierte Spalte
• Neuer Spal­tenname: Start
• Benutzerdefinierte Spal­tenformel:

  = if Date.Day([Erster Tag]) = 1 then [Erster Tag]
    else Date.AddMonths(Date.StartOfMonth([Erster Tag]), 1)

• Bei mein­er Vorge­hensweise der Lösungs­find­ung ist es eigentlich nicht erforder­lich, den Ulti­mo des let­zten Monats zu berech­nen. Ich mache das hier ein­fach ein­mal, um Ihnen auch diese Funk­tion der Sprache M aufzuzeigen. Die Über­schrift in der neuen Benutzerdefinierten Spalte wird naturgemäß Ende sein und die Formel:
  =Date.EndOfMonth([Letzter Tag])

Prinzip­iell wäre es in diesem Sta­di­um möglich, in Plain Excel oder in VBA die Funk­tion DATEDIF/Date­d­iff zu ver­wen­den. Aber wenn wir schon ein­mal in Pow­er Query sind, sollte das Ergeb­nis auch auf dieser Ebene berech­net wer­den. Meine erste Idee war, per Formel die Anzahl der Dif­ferenz-Tage zu berech­nen, das Ergeb­nis durch 30 divi­dieren und dann run­den. Das wird in den meis­ten Fällen auch zu einem kor­rek­ten Ergeb­nis führen. Aber in den meis­ten Fällen hat ja auch die von den anderen Helfern vorgeschla­gene Formel in Plain Excel oder VBA hinge­hauen, nur in Aus­nah­me­fällen nicht. Und aus dem Grunde finde ich den hier­vor aufgezeigten Lösungsweg nicht ganz passend, denn es kann nicht aus­geschlossen wer­den, dass der Zeitraum ein­mal sehr viele Jahre umfasst und dadurch die 30-Tage-Berech­nung zu Fehlergeb­nis­sen führen kann. Den­noch will ich Ihnen nichts ver­heim­lichen: Die Formel für solch eine Berech­nung würde dann so ausse­hen:
=Number.Round(Number.From([Ende]-[Start])/30)

Hin­weis: Wenn Sie einen möglichst genauen Divi­sor nutzen wollen, dann divi­dieren Sie mit 30,436875. Zugegeben, es gibt noch weit­ere Meth­o­d­en mit einem kleinen Pro­gramm in der Sprache M die Berech­nung sauber durchzuführen. Aber ich wäh­le da den etwas prag­ma­tis­cheren Weg und arbeite mit ein­er Hil­f­ss­palte, wo ich jeden Tag des benan­nten Zeitraums einzeln aufliste. Und daraus gener­iere ich dann in ein­er neuen Spalte für jede Zeile einen Tex­twert, der dem For­mat MM-JJJJ entspricht. Anschließend berechne ich daraus die Anzahl der Unikate und das Ergeb­nis ist die kor­rek­te Anzahl der Monate. Hier Schritt für Schritt eine kurze Anleitung:

• Spalte hinzufü­gen | Benutzerdefinierte Spalte
• Als neuen Spal­tenna­men gebe ich beispiel­sweise Anzahl Monate ein.
• Die Benutzerdefinierte Spal­tenformel 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”)

• Erweit­ern Sie nun die Über­schrift der Spalte Anzahl Monate durch einen Klick auf den Dop­pelpfeil
• Wählen Sie im Drop­down die obere Möglichkeit: Auf neue Zeilen ausweit­en
• Per Kon­trolle wer­den sie nun erken­nen, dass die Tabelle im Pow­er Query-Edi­tor für jeden Tag des Zeitraums ab dem ersten vollen Monat bis hin zum let­zten Tag des let­zten Monats einen fort­laufend­en Datum-Ein­trag enthält.
• Start/Home | Ver­wal­ten | Ver­weis, um ein Ver­weis-Dup­likat der Abfrage zu erstellen.
• Geben Sie der neuen Abfrage den Namen volle Monate.
• Zur Erstel­lung der Textspalte im (Excel-) For­mat MM-JJJJ wählen Sie im Menüband wiederum Benutzerdefinierte Spalte, Spal­tenname kön­nte auch MM-JJJJ sein und geben dann diese Formel exakt so ein:
  =Date.ToText([Anzahl Monate], "MM-yyyy")
• Da Sie ja nur das Ergeb­nis der ein­ma­li­gen Ein­träge in dieser Spalte brauchen, ein Recht­sklick in die Über­schrift und wählen Sie im Kon­textmenü Dup­likate ent­fer­nen. Das Ergeb­nis wird sich so darstellen:

Die gefilterte Liste, nur Einzel­w­erte der Monate

• Die Spalte bleibt markiert. Wählen Sie im Menü Trans­formieren das Sym­bol Sta­tis­tiken | Ein­deutige Werte zählen.
• Das Ergeb­nis ist vielle­icht etwas über­raschend … 😯 

Die Liste muss noch zu ein­er Tabelle kon­vertiert wer­den

• Klick­en Sie auf Zu Tabelle, um die Liste zu ein­er auswert­baren Tabelle umzu­for­men.
• Klick­en Sie hier auf das ganz linke Sym­bol Zu Tabelle und Pow­er Query erstellt eine Tabelle mit ein­er Spalte, Über­schrift Column1 und in der einzi­gen Zeile ste­ht die berech­nete Anzahl der Monate: 14.

▲ nach oben …

Noch offen ist die Berech­nung des Anteils im ersten Monat für den Fall, dass der reale Start-Tag nicht der Monat­ser­ste ist son­dern irgend­wann im Laufe des Monats liegt. Da sollen dann ja nur jene Tage prozen­tu­al berech­net wer­den, die auch im Start-Monat liegen. Dabei ist zu berück­sichti­gen, dass eine wirk­lich tagge­naue Berech­nung vorgenom­men wer­den soll. Erstellen Sie also dieser neuen Mini-Tabelle eine neue Spalte und vergeben für die exak­te Berech­nung der Monate eine beliebige Über­schrift; ich ver­wende Monate (exakt). Hier trage ich nun fol­gende Formel ein:
=14+2/31

Erstaunt? Ja, das dür­fen Sie auch sein. Klar, die Berech­nung ist math­e­ma­tisch kor­rekt, aber for­mal natür­lich Unsinn. Denn ich habe die durch Augen­schein am Bild­schirm gele­se­nen Werte von Hand in die Formel eingegeben. Und das kann es ja nun nicht wirk­lich sein, denn wenn die Ursprungs­dat­en nicht 14 Monate umfassen, 2 Rest-Tage in einem Monat mit 31 Tagen haben, dann stimmt das Ergeb­nis natür­lich nicht. 🙁 

Okay, der Weg dahin ist im „wirk­lichen Pow­er Query-Leben” doch etwas kom­plex­er. Ich werde Ihnen hier ein­mal (aus­nahm­sweise im non-$$-Bereich) den Weg sehr detail­liert, ein­schließlich der Zwis­chen­schritte vorstellen. (Sie dür­fen sich auch gerne bedanken, siehe ganz am Ende des Beitrages).

Im ersten Schritt wer­den Sie sich in dieser Mini-Abfrage das Datum des Start-Tages per Pow­er Query holen. Es gibt mehrere Möglichkeit­en; die offen­sichtlich­ste ist gewiss, dass dieser Wert In der Tabelle Auswer­tungszeitraum in der Spalte Start ste­ht. Aber so wie in Plain Excel, mit einem = und dann ein Klick in die Ziel-Zelle geht das hier nicht. Sie kön­nen den Wert aber in ein­er Benutzerdefinierten Spalte abfra­gen:
=Table.First(Auswertungszeitraum, "Start")

Der Klarheit wegen gebe ich dieser Spalte vor­erst ein­mal die Über­schrift Teil-Monat%, denn der verbleibende Teil des Monats soll ja als Prozent-Wert berech­net wer­den. Das Ergeb­nis wird sich dann im ersten Schritt so darstellen:

Das Ergeb­nis der Funk­tion ist ein Record (Daten­satz)…

Es wird Sie gewiss nicht über­raschen, dass Sie im näch­sten Schritt die Über­schrift Teil-Monat% durch einen Klick auf den Dop­pelpfeil  erweit­ern wer­den. Ent­fer­nen Sie im Dia­log alle Häkchen (ein­schließlich Ursprüngliche Spal­tenna­men…) und set­zen das Häkchen bei Erster Tag, wo ja der erste Tag des Zeitraums (also auch des ersten Monats) ver­merkt ist:

Nur dieses eine Kästchen bleibt markiert

Nach der Bestä­ti­gung durch OK wird die Spalte automa­tisch zu Erster Tag umbe­nan­nt (also dem Namen der ursprünglichen Spalte) und der Inhalt ist auch jen­er Wert, den Sie gewiss erwartet haben: 30.01.2018. Damit ist die Grund­lage gelegt, um die (in diesem Fall)  die restlichen 2 von 31 Tagen des Monats zu berech­nen. Natür­lich geht das auch mit ein­er Benutzerdefinierten Spalte, welch­er ich den Namen % vom Monat gebe:
=(Number.From(Date.EndOfMonth([Erster Tag]) - [Erster Tag]) +1) / Date.Day(Date.EndOfMonth([Erster Tag]))

Die Umwand­lung durch die Funk­tion Number.From ist erforder­lich, da eine Addi­tion zu Werten im Datum-For­mat zu einem Fehler führt. Die Sprache M (also Pow­er Query) ist in Sachen Daten­typ recht peni­bel. – Wenn ich den Wert der „aktiv­en” Tage durch die Anzahl der Tage im Monat (Date.Day(Date.EndOfMonth) divi­diere, dann habe ich den prozen­tualen Wert des Anteils. Das Ergeb­nis entspricht ²⁄₃₁, also (als „nor­male” Dez­i­malzahl aus­ge­drückt) 0,064516129032258063.

Im let­zten Schritt markieren Sie die bei­den Spal­ten volle Monate und % vom Monat, Spalte hinzufü­gen | Sta­tis­tiken | Summe und die neue, umge­formte Spalte Addi­tion enthält das Ergeb­nis. – Und wie auch im Forum-Thread kommt zum Schluss die Erk­lärung, wozu diese Zahl gebraucht wird: Für einen Ver­trag wird basierend auf der Monats-Laufzeit ein zu berech­nen­der Betrag berech­net. Der erste Monat wird  anteilig berech­net, der let­zte Monat wird stets voll in Anrech­nung gebracht.

Das gewün­schte Ergeb­nis ist erre­icht. Sie kön­nen nun die Über­schrift Ihren Wün­schen anpassen, die anderen Spal­ten löschen und über Schließen & laden oder Schließen & laden in… das Ergeb­nis in ein Excel Arbeits­blatt schreiben. – Zugegeben, beim ersten Mal ist es eine ganze Menge Arbeit. Aber wegen der absoluten Genauigkeit ist der Aufwand gerecht­fer­tigt. Zumal Sie mit einem einzi­gen Mausklick auf Aktu­al­isieren bei geän­derten Quell­dat­en sofort ein angepasstes Ergeb­nis haben.