Kontakt

Nutzen Sie dieses Kontaktformular oder schreiben Sie mir direkt per E-Mail.

Team von Finanzexperten, die auf dem Computerbildschirm ein Dashboard für Geschäftsanalytik (BA) oder Intelligence (BI) diskutieren, in dem die Umsatzdaten und Betriebsdaten sowie die wichtigsten Leistungsindikatoren (KPI) dargestellt werden

Power BI – So meistern Sie anspruchsvolle Berechnungen mit der Zeit

  • Zeiten über 24 Stunden
  • Zeiten des Vormonats
  • Prozentuale Abweichungen
Jan Trummel - Coach für  Excel und  Power BI

Jan Trummel – Trainer für Excel und Power BI – erzählt hier wie Sie Ihre Arbeit effektiver erledigen können.

Der Titel ist bewusst doppeldeutig formuliert: Es dauert eine Zeit, bis man Berechnungen mit Zeit-Werten sicher beherrscht. Was ist mit dem Vergleich verschiedener Perioden? Wie werden prozentuale Abweichungen von Zeit-Werten berechnet? Und was machen Sie bei Zeitangaben über 24 Stunden? Dieser Beitrag liefert Antworten.

Die Ausgangslage

Schauen Sie sich die folgenden Ausgangsdaten einmal an.

Diese Excel-Tabelle können Sie kostenlos herunterladen.

Sie sehen die Bearbeitungszeiten der Teams A, B und C für die Monate April und Mai 2021. Sie sollen mit Power BI einen Bericht erzeugen, in welchem sich die Bearbeitungszeiten der Teams für einen bestimmten Monat analysieren lassen. Folgendes soll der Bericht leisten:

Der Berichtsleser soll das Jahr und den Monat des Analysezeitraums frei wählen können. Der Bericht soll ihm dann die gesamte Bearbeitungszeit eines jeden Teams im gewählten Zeitraum anzeigen. Zusätzlich soll der Leser auch die gesamte Bearbeitungszeit des Vormonats sehen, darüber hinaus die prozentuale Abweichung zwischen den Zeiten des gewählten und des Vormonats.

Hier sehen Sie, wie der Bericht im fertigen Zustand aussieht. Ich werde Sie im folgenden Schritt für Schritt dorthin führen. Legen wir los!

Mit dem fertigen Power BI-Bericht können Sie die Zeiten der Teams analysieren.

Mit Power BI erstellen Sie in kürzester Zeit interaktive Berichte die Führungskräfte begeistern werden! Sie möchten lernen, wie das geht? Entdecken Sie mein Training!

Daten laden und modellieren

Sie können die Excel-Tabelle einfach über die gleichnamige Schaltfläche herunterladen. Importieren Sie die Daten anschließend in Power BI Desktop. Sie brauchen außerdem eine Kalendertabelle, um die Bearbeitungszeiten des Vormonats berechnen zu können (dazu später mehr).

Verbinden Sie die geladene Tabelle mit der Kalendertabelle über die Felder Datum und Datum.

Zeigt zwei Tabellen im Datenmodell von Power BI, die miteinander verbunden sind
Verbinden Sie die Tabelle mit der Kalendertabelle

Das Basismeasure

Wir erzeugen jetzt das erste Measure. Es soll lediglich die Bearbeitungszeiten aufaddieren, weshalb die Funktion SUM zum Einsatz kommt. Dieses Measure verwenden Sie später als Baustein in anderen Measures. Es handelt sich also um ein sogenanntes Basismeasure:

Gesamtzeit = SUM('Bearbeitungszeiten Teams'[Bearbeitungszeit])

Fangen Sie jetzt gleich an, die benötigten Visualisierungen auf der Berichtseite zu platzieren. Zunächst einmal brauchen Sie zwei Datenschnitte. Mithilfe dieser Visualisierungen wird der Berichtsleser den Auswertungszeitraum wählen können. In den ersten Datenschnitt legen Sie das Feld Jahr aus der Kalendertabelle, in den zweiten Datenschnitt das Feld Monat (den Monatsnamen), ebenfalls aus der Kalendertabelle.

Stellen Sie für beide Datenschnitte die Einfachauswahl ein, damit der Berichtsleser nur ein Jahr und nur einen Monat wählen kann. Klicken Sie jetzt das Jahr 2021 und den Monat Mai an.

Fügen Sie anschließend eine Matrix-Visualisierung in die Berichtseite ein. Ziehen Sie das Feld Team in den Zeilen-Bereich, das Measure Gesamtzeit legen Sie in den Werte-Bereich. Sehen Sie das Problem?

Das Measure Gesamtzeit zeigt für das Teams C den falschen Wert!

Zeiten über 24 Stunden

Das Team C hatte im Mai eine Bearbeitungszeit von insgesamt 42:35 (42 Stunden und 35 Minuten). Das Basismeasure zeigt Ihnen aber fälschlicherweise 18:35 an. Das Problem liegt in der Uhrzeitformatierung. Sie bewirkt, dass bei Werten über 24 Stunden der Zähler sozusagen wieder bei 0 startet. In Excel könnten Sie der Herausforderung ganz einfach mit der benutzerdefinierten Formatierung und den eckigen Klammern ([hh]:mm:ss) begegnen. In Power BI gibt es das leider (noch) nicht. Hier hilft nur ein weiteres Measure:

Gesamtzeit Text = 
INT([Gesamtzeit])*24 
+ 
INT(([Gesamtzeit]-INT([Gesamtzeit]))*24)
&
FORMAT([Gesamtzeit]-INT([Gesamtzeit]),":nn:ss")

Um diese Formel zu verstehen, sollten Sie wissen, wie Power BI mit Uhrzeiten umgeht. Uhrzeiten werden, wie in Excel, als Dezimalzahlen gespeichert. Ein Tag hat dabei den Wert 1, eine Stunde den Wert 1/24, 1 Minute entspricht 1/1440 (1440 = 24 * 60) und eine Sekunde ist 1/86400 (86400 = 24 * 60 * 60). 42:35 entspricht übrigens der Zahl 1,774305… .

Schauen wir uns die Formel einmal Schritt für Schritt an:

INT([Gesamtzeit])*24 

Diese Berechnung ermittelt zunächst einmal die Anzahl der Stunden von ganzen Tagen. Dazu müssen Sie sich die Zahl vor dem Komma nehmen und die Nachkommastellen eliminieren. Dies gelingt mit der Funktion INT, welche eine Zahl auf die nächstkleinere Ganzzahl rundet. Das Ergebnis multiplizieren Sie dann mit 24 und schon haben Sie die Stundenanzahl.

Es geht weiter mit der folgenden Rechnung:

INT(([Gesamtzeit]-INT([Gesamtzeit]))*24)

Jetzt widmen Sie sich den Nachkommastellen. Diese stehen für „angebrochene“ Tage. Die Nachkommastellen erhalten Sie, indem Sie von der Zahl die Ganzzahl (wieder mit INT) abziehen. Das Ergebnis dieser Subtraktion multiplizieren Sie mit 24, um die Anzahl der Stunden zu erhalten. Das errechnete Produkt runden Sie dann wieder auf die nächstkleinere Ganzzahl, da hier Minuten und Sekunden nicht interessieren.

Die Stunden der ganzen Tage und die Stunden des angebrochenen Tages addieren Sie und erhalten so die Gesamtzahl der Stunden.

Zum Schluss kommen wir zu den Minuten und Sekunden:

FORMAT([Gesamtzeit]-INT([Gesamtzeit]),":nn:ss")

Hier bedienen Sie sich der Funktion FORMAT. Übergeben Sie ihr im ersten Argument wieder die Nachkommastellen (also wieder die Zahl abzüglich der Ganzzahl). Mit der Angabe ":nn:ss" im zweiten Argument formatieren Sie diese in Minuten und Sekunden.

Die Minuten und Sekunden verketten Sie mithilfe des &-Zeichens mit der Stundenzahl. Fertig ist die Uhrzeit!

Beachten Sie, dass dieses Measure einen Text liefert, mit dem Sie nicht mehr weiter rechnen können. Er dient lediglich der Anzeige. Entfernen Sie jetzt in der Matrix das Measure Gesamtzeit und fügen Sie stattdessen das Measure Gesamtzeit Text ein. Sie sehen bei Team C nun den korrekten Wert 42:35:

Das neue Measure Gesamtzeit Text zeigt jetzt den korrekten Wert für das Team C an!

Zeiten des Vormonats

Jetzt kommen wir zu den Bearbeitungszeiten des Vormonats. Wir benötigen ein neues Measure, dessen Code Sie hier sehen können:

Gesamtzeit Text Vormonat = 
CALCULATE([Gesamtzeit Text],PREVIOUSMONTH(Kalender[Datum]))

In der Formel kommt die Funktion PREVIOUSMONTH zum Einsatz. Wichtig ist, dass Sie der Funktion die Spalte Datum der Kalendertabelle übergeben. Zeitintelligenzfunktionen brauchen immer eine Kalendertabelle, welche einen bestimmten Datumsbereich lückenlos auflistet. PREVIOUSMONTH liefert alle Datumsangaben, die relativ zum im Bericht gewählten Zeitraum einen Monat in der Vergangenheit liegen. Diese Datumsangaben benutzt CALCULATE, um den Filterkontext des Basismeasures Gesamtzeit zu manipulieren. Die Formel errechnet jetzt immer die Summe der Bearbeitungszeiten des Vormonats. Ziehen Sie das Measure in die Matrix:

Jetzt werden auch die Zeiten des Vormonats angezeigt.

Prozentuale Abweichungen der Zeit

Jetzt fehlt nur noch ein Measure, dass die Abweichungen zwischen gewähltem und Vormonat in Prozenten ausrechnet. Da Sie mit den beiden eben erzeugten Measures nicht rechnen können - diese liefern ja Texte - greifen wir hier auf das Basismeasures Gesamtzeit zu. Außerdem brauchen Sie ein Measure, dass die Gesamtzeiten des Vormonats als Zahl ausgibt:

Gesamtzeit Vormonat = 
CALCULATE([Gesamtzeit],PREVIOUSMONTH(Kalender[Datum]))

Die Formel ähnelt der von Gesamtzeit Vormonat Text. Der einzige Unterschied ist, dass Sie in CALCULATE das Basismeasure verwenden.

Es folgt das Measure mit der prozentualen Abweichung:

Abweichung = 
DIVIDE(
    [Gesamtzeit] - [Gesamtzeit Vormonat], 
    [Gesamtzeit Vormonat]
)

Für die sichere Division benutzen Sie die Funktion DIVIDE. Im ersten Argument, dass dem Zähler (Dividend) entspricht, ziehen Sie von dem Measure Gesamtzeit das Measure Gesamtzeit Vormonat ab. Im zweiten Argument, welches den Nenner (Divisor) darstellt, geben Sie das Measure Gesamtzeit Vormonat an. Das dritte Argument können Sie leer lassen. Im Fehlerfall gibt die Funktion dann einfach ein Leerzeichen aus. Stellen Sie die Formatierung des Measures jetzt noch um in Prozent und fügen Sie es in die Matrix ein.

Hier sehen Sie noch einmal den fertigen Power BI-Bericht.

Herzlichen Glückwunsch! Sie haben es geschafft. Ich hoffe, dass Ihnen Berechnungen mit Zeit-Werten zukünftig deutlich leichter von der Hand gehen.

Möchten Sie mehr über Power BI lernen? Dann empfehle ich Ihnen meinen Training
Power BI - Perfekt für Einsteiger!


Verwendete Quellen

Svens Excel-Welt. Rechnen mit Zeiten (Uhrzeit, Stunden, Minuten, Dauer) (Abgerufen am 27.05.2021)