Teil 2 – Ertragswertanalyse mit Planner Premium (Spalten & Tabellen anpassen)

In meinem letzten Artikel habe ich die Grundvoraussetzungen gezeigt, die benötigt werden, um mit den Informationen aus Planner Premium eine Ertragswertanalyse zu erstellen. Dabei bin ich auf die Erfassung von Kosten in Planner Premium eingegangen und habe gezeigt, wie du die benötigten Tabellen in Power BI Desktop entsprechend lädst. Heute werden wir in meinem Blog die Spalten und Tabellen anpassen, damit wir später die Ertragswertanalyse erstellen können.

Hinweis: Diese Blog-Reihe baut aufeinander auf und du solltest dir unbedingt auch die vorherigen Artikel anschauen.

Anpassen der Spalten in den Tabellen

Als erstes passen wir die Tabelle „msdyn_projecttask“ an. In dieser Tabelle werden alle Taskinformationen gespeichert. Da wir für die Ertragswertanalyse nur Arbeitspakete benötigen, nicht aber Meilensteine oder Sammelvorgänge, filtern wir diese als erstes heraus. Öffne den Power Query Editor. Klicke hierzu im Ribbonmenü auf die Registerkarte „Start“ und klicke dort auf den Button „Datentransformieren“.

Klicke als nächstes auf der linken Seite auf die Tabelle „msdyn_projecttask“. Suche in der Tabelle die Spalte „msdyn_ismilestone“ und öffne das Filtermenü der entsprechenden Spalte. Wende hier den Filter an, indem du nur die „False“-Werte auswählst. Schließe dann das Filtermenü, indem du einfach einen Bereich neben dem Menü klickst.

Setze jetzt den gleichen Filter in der Spalte „msdyn_summary“. Nachdem du die Filter gesetzt hast, kannst du beide Tabellenspalten löschen, da sie für unsere Ertragswertanalyse nicht mehr benötigt werden. Keine Angst, der Filter bleibt weiterhin erhalten.

Als nächstes passt du die Spalten „msdyn_finish“ und „msdyn_start“ an. In der Standardformatierung wird das Datum mit der Uhrzeit verwendet. Wir benötigen für die Analyse nur das Datum ohne die Uhrzeit. Formatiere die jeweilige Spalte wie folgt: Klicke auf die Spalte „msdyn_start“. Klicke auf der linken Seite auf den Button für die Spaltenformatierung und wähle in dem sich öffnenden Kontextmenü die Option „Datum“ aus.

Ändere das Datumsformat ebenfalls für die Spalte „msdyn_finish“, wie gerade bei der Spalte „msdyn_start“. Zum Schluss musst du nun noch alle leeren Werte aus der Spalte „msdyn_start“ filtern. Klicke hierzu wieder auf der rechten Seite der Spalte auf das Filtermenü und entferne den Haken bei dem Wert „NULL“.

Hinweis: Warum entferne ich nicht die Null-Werte aus der Spalte msdyn_finish? In Planner Premium wird immer vom Anfangsdatum ausgegangen. Sollte also kein Wert in der Spalte msdyn_start stehen, wird auch kein Wert in der msdyn_finish stehen. Aus diesem Grund setze ich den Filter auf die Start-Spalte.

Da die Spalten Start- und Enddatum auch in der Tabelle „msdyn_projectbaselinetaskdata“ enthalten sind, musst du auch hier das Datumsformat ändern. Allerdings heißen die Spalten dort nicht msdyn_start und msdyn_finish, sondern msdyn_taskfinishdate und msdyn_taskstartdate. Klicke also auf der linken Seite im Power Query Editor auf die Tabelle „msdyn_projectbaselinetaskdata“ und ändere auch hier das Datum mit Uhrzeit auf nur Datum, wie gerade zuvor beschrieben. Filtere auch hier dann wie zuvor in der Spalte msdyn_taskstartdate die „NULL“-Werte heraus.

Transformation der Tabellen msdyn_projecttask und msdyn_projectbaselinetaskdata

Leider ist es so, dass wir mit der Standardsortierung der Zeilen in den Tabellen „msdyn_projectbaselinetaskdata“ und „msdyn_projecttask“ nichts anfangen können, denn wir haben in beiden Tabellen nur ein Anfangs- und ein Enddatum. Der Abstand zwischen beiden Spalten ist die jeweilige Dauer, die ein Arbeitspaket besitzt. Was wir aber für die Erstellung einer Ertragswertanalyse benötigen, ist für jeden Tag einen Eintrag in den Tabellen. Ich nenne das Ganze auch immer gerne „Zeit als Tag“. Solltest du meinen Blog regelmäßig lesen, wirst du dich bestimmt daran erinnern, dass ich vor einiger Zeit einen Artikel geschrieben habe, wie du den JSON-String, der in der Tabelle „msdyn_ressourcenassignment“ enthalten ist, so transformieren kannst, dass du die Aufwände auslesen kannst, die einer Ressource in einem Arbeitspaket zugewiesen wurden. Leider können wir mit diesem Lösungsansatz hier nichts anfangen, denn sobald ein Arbeitspaket als erledigt gekennzeichnet wird, wird der entsprechende JSON-String aus der Spalte entfernt und es wird eine „NULL“ eingetragen. Wir benötigen aber die Werte auch rückwirkend für unsere EVA-Analyse.

Aus diesem Grund müssen wir einen anderen Weg einschlagen. Wir berechnen als erstes den Zeitraum in Tagen zwischen dem Anfangs- und dem Endzeitpunkt. Die berechnete Dauer schreiben wir dann in einer Liste und fügen diese Liste einer neuen Spalte hinzu. Danach extrahieren wir die Liste in Zeilen in der gewünschten Tabelle und somit erhalten wir unser Ergebnis.

Klicke auf der linken Seite im Power Query Editor auf die Tabelle „msdyn_projecttask“. Klicke im Ribbonmenü auf die Registerkarte „Spalte hinzufügen“. Klicke dann auf den Button „Benutzerdefinierte Spalte“. Gib als Spaltennamen „ZeitAlsTag“ ein und trage dann den folgenden Code ein:

{Number.From (Date.From([msdyn_start]))..Number.From(Date.From([msdyn_finish]))}

Hierzu eine kurze Erläuterung zur Funktion der Formel. Die Formel generiert eine Liste von Zahlen, die jeden Tag zwischen dem Start- und Enddatum repräsentieren. Dabei wird das Startdatum in eine Zahl umgewandelt und bis zum Enddatum hochgezählt. Deine Ansicht sollte wie folgt aussehen.

Die Spalte sollte nun wie gewünscht hinzugefügt worden sein. Die Liste enthält nun die gewünschten Tage zwischen dem Anfangs- und dem Enddatum als Datum. Als nächstes expandieren wir die Liste. Klicke auf das Expandieren-Icon in der oberen rechten Ecke des Spaltenkopfes und wähle im Kontextmenü die Option „Auf neue Zeilen ausweiten“.

Zum guten Schluss musst du nun noch das Format der Spalte „ZeitAlsTag“ von Text auf Datum ändern. Klicke hierzu auf das Spaltenformatierungssymbol gleich neben dem Namen „ZeitAlsTag“ im Spaltenkopf und wähle dort „Datum“ aus.

Hinweis: Führe jetzt die gleichen Schritte in der Tabelle msdyn_projectbaselinetaskdata aus. Achte bei der Formel darauf, dass du msdyn_start und msdyn_finish durch die Spalten msdyn_taskstartdate und msdyn_taskfinishdate ersetzt!

Beide Tabellen sollten nun die Spalte „ZeitAlsTag“ besitzen und die Ansicht sollte wie folgt aussehen.

Damit du zu einem späteren Zeitpunkt eventuell eine bessere Fehleranalyse durchführen kannst, empfehle ich noch eine weitere Spalte „DaysBetween“ hinzuzufügen. Diese soll die Tage ohne Arbeitstage zwischen dem Anfangsdatum und dem Enddatum ausgeben.

Um diese Spalte anzulegen, klicke erneut auf die Registerkarte „Spalte hinzufügen“. Gib als Spaltennamen „DaysBetween“ ein und dann die folgende Formel:

List.Count(List.Select(List.Dates(Date.From([msdyn_start]), Duration.Days(Date.From([msdyn_finish]) - Date.From([msdyn_start])) + 1, #duration(1,0,0,0) ), each Date.DayOfWeek(_, Day.Monday) < 5 ))

Was berechnet diese Formel? Die Formel zählt die Anzahl der Werktage zwischen zwei angegebene Daten. Dabei werden Wochenenden ausgelassen, sodass nur die Tage von Montag bis Freitag berücksichtigt werden.

Formatiert abschließend die neue Spalte als „Zahl“. Klicke hierzu wieder auf das kleine Icon gleich vor den Spaltenamen „DaysBetween“ und wähle im Kontextmenü die Option „Dezimal Zahl“ aus.

Passe jetzt die Formel mit den Spaltennamen aus der Tabelle „msdyn_projectbaselinetaskdata“ erneut an. Ersetze die Spalten „msdyn_start“ und „msdyn_finish“ durch die Spaltennamen „msdyn_taskstartdate“ und „msdyn_taskfinishdate“. Füge dann die Spalte „DaysBetween“ auch der Tabelle „msdyn_projectbaselinetaskdata“ hinzu. Klicke hierzu auf der linken Seite auf die Tabelle „msdyn_projectbaselinetaskdata“, klicke dann erneut auf den Button „Benutzerdefinierte Spalte“, trage als Name ebenfalls „DaysBetween“ ein und füge dann die von dir geänderte Formel im Formeleditor hinzu. Nun solltest du in beiden Tabellen auch die Spalte „DaysBetween“ haben.

Nun haben wir eine neue Herausforderung. Durch das Extrahieren des Anfangs- und Enddatums als „ZeitAlsTag“ werden auch die Wochenenden mit berechnet. Würden wir nun die Rechnung machen Aufwand / Dauer x Stundensatz, hätten wir das Problem, dass an Samstagen und Sonntagen auch der Wert mit berechnet wird. Das wollen wir aber nicht. Aus diesem Grund müssen wir eine weitere Spalte hinzufügen, die uns ausgibt, ob das Datum in „ZeitAlsTag“ ein Samstag oder Sonntag ist. Wenn nicht, dann schreibe in die Spalte „Wochentag“. Hierzu benötigen wir also eine weitere Spalte in den Tabellen.

Klicke im Power Query Editor auf die Registerkarte „Spalte hinzufügen“ in der Tabelle „msdyn_projectbaselinetaskdata“. Klicke erneut auf den Button „Benutzerdefinierte Spalte“. Gib als Spaltennamen „Wochentag“ ein und als Spaltenformel die folgende Formel:

if Date.DayOfWeek([ZeitAlsTag], Day.Monday) = 5 then "Samstag" else if Date.DayOfWeek([ZeitAlsTag], Day.Monday) = 6 then "Sonntag"else "Wochentag"

Natürlich möchte ich dir auch kurz diese Formel erklären. Der Code überprüft den Wochentag in der Spalte „ZeitAlsTag“ und gibt basierend darauf eine entsprechende Zeichenkette zurück. Wenn es sich in der Spalte „ZeitAlsTag“ um einen Samstag handelt, wird „Samstag“ zurückgegeben, ist es ein Sonntag, dann „Sonntag“. Alles andere ist ein Wochentag.

Natürlich benötigen wir diese Spalte auch in der Tabelle „msdyn_projecttask“. Klicke also auf der linken Seite im Power Query Editor auf die Tabelle „msdyn_projecttask“, klicke unter der Registerkarte „Spalte hinzufügen“, gib als Spaltennamen genau wie vorher „Wochentag“ ein und kopiere dann die Formel auch hier in den Code-Editor. Da in beiden Tabellen die Spalte „ZeitAlsTag“ existiert, musst du keine Codeanpassungen vornehmen.

Das war’s auch schon mit diesem Blogartikel. Im nächsten Artikel werden wir noch zwei zusätzliche Tabellen erstellen, die wir zur Erstellung der Ertragswertanalyse benötigen. Solltest du Fragen zu diesem Artikel haben, schreib mir gerne eine E-Mail oder eine persönliche Nachricht bei LinkedIn.