Ruhezeiten im Personalbereich mit Measure in Power BI berechnen

Sie möchten mit Power BI Desktop ermitteln, ob Ihr Personal die vorgeschriebene Ruhezeit zwischen den Arbeitstagen eingehalten hat. Dies ist erfüllt, wenn zwischen dem Ende des letzten Arbeitstages und dem Beginn des nächsten Arbeitstages mindestens 11 Stunden liegen. Dies gelingt Ihnen mit dem hier vorgestellten Measure!

Die Daten

Für jeden Mitarbeitenden können pro Arbeitstag mehrere Zeilen in der Tabelle existieren. Beispiel: Eine Person beginnt um 8:00 Uhr zu arbeiten und macht um 12:00 Uhr für eine halbe Stunde Pause. Dann arbeitet sie bis 16:00 Uhr weiter und geht anschließend in den Feierabend. In der Tabelle sind diese Vorgänge mit 3 Datensätzen erfasst:  

Hier nun die komplette Tabelle Anwesenheitsperioden:

Verstöße gegen die Ruhezeiten

In den Beispieldaten hat die Person A am 05.09.2023 die Ruhezeiten nicht eingehalten. Sie hat am 04.09.2023 um 18:30 Uhr aufgehört zu arbeiten, am 05.09.2023 aber schon um 5:00 Uhr morgens wieder mit der Arbeit begonnen. Dazwischen liegen gerade einmal 10,5 Stunden, also weniger als die vorgeschriebenen 11 Stunden!

Diese Verstöße händisch herauszufinden ist sehr mühselig. Sie werden gleich erfahren, wie Sie mit einem Measure diese Aufgabe zeitsparend von Power BI erledigen lassen können!

Das Datenmodell

Im Datenmodell ist die Tabelle Anwesenheitsperioden mit einer Datumstabelle namens Kalender über eine 1-n-Beziehung verbunden: 

Das Measure

Mit folgender DAX-Anweisung können Sie berechnen, ob das Personal die Ruhezeiten eingehalten hat:

Ruhezeiten = 
VAR __Ende_letzter_Arbeitstag =
MAXX(
    FILTER(
        ALL(Anwesenheitsperioden),
        Anwesenheitsperioden[Person] = SELECTEDVALUE(Anwesenheitsperioden[Person]) &&
        Anwesenheitsperioden[Tag] < MAX(Kalender[Datum])
    ),
    Anwesenheitsperioden[Tag] + Anwesenheitsperioden[Ende]
)
 
VAR __Beginn_aktueller_Arbeitstag =
MINX(
    FILTER(
        ALL(Anwesenheitsperioden),
        Anwesenheitsperioden[Person] = SELECTEDVALUE(Anwesenheitsperioden[Person]) &&
        Anwesenheitsperioden[Tag] = MAX(Kalender[Datum])
    ),
    Anwesenheitsperioden[Tag] + Anwesenheitsperioden[Beginn]
)
 
VAR __Differenz =
VALUE(__Beginn_aktueller_Arbeitstag - __Ende_letzter_Arbeitstag)
 
VAR __Ausgabetext =
IF(__Differenz < 0.4583, "Ruhezeit nicht eingehalten!", "ok")
 
VAR __Ergebnis =
IF(
    ISBLANK(MAX(Anwesenheitsperioden[Tag])) ||
    NOT(HASONEVALUE(Anwesenheitsperioden[Person])), 
    BLANK(), 
    __Ausgabetext
)
 
RETURN
__Ergebnis

Mithilfe von Variablen können Sie die Berechnung in Einzelschritte zerlegen. Dies erleichtert Ihnen das Schreiben von DAX-Code und erlaubt Ihnen auch, verschiedene Tests durchzuführen. Dazu geben Sie nach dem Schlüsselwort RETURN einfach den Namen der Variablen ein, die das Measure als Ergebnis ausgeben soll.

Im Folgenden gehe ich auf die einzelnen Berechnungen ein:

Ermitteln Sie das Ende des letzten Arbeitstages

Beachten Sie, dass der letzte Arbeitstag nicht immer der gestrige Tag ist! Zwischen 2 Arbeitstagen können Wochenenden oder Feiertage liegen. In unserem Fall liegen zwischen dem 01.09.2023 und dem 04.09.2023 die arbeitsfreien Wochenendtage Samstag und Sonntag.

VAR __Ende_letzter_Arbeitstag =
MAXX(
    FILTER(
        ALL(Anwesenheitsperioden),
        Anwesenheitsperioden[Person] = SELECTEDVALUE(Anwesenheitsperioden[Person]) &&
        Anwesenheitsperioden[Tag] < MAX(Kalender[Datum])
    ),
    Anwesenheitsperioden[Tag] + Anwesenheitsperioden[Ende]
)

Mit dem Ausdruck ALL(Anwesenheitsperioden) machen Sie die komplette Tabelle Anwesenheitsperioden sichtbar. Die Funktion ALL entfernt alle Filter, die sich auf die Tabelle auswirken können. Mit der Funktion FILTER können Sie die Tabelle anschließend auf die in der jeweiligen Berichtszeile stehende Person filtern. Bei der Filterung auf das Datum müssen Sie mit dem Operator kleiner als (<) arbeiten, damit die gefilterte Tabelle nur Zeilen enthält, bei denen die Tage vor dem Datum im aktuellen Filterkontext liegen.  

Die von FILTER zurückgegebene Tabelle steht im 1. Argument der Funktion MAXX. FILTER stellt also die Tabelle zur Verfügung, aus der sich MAXX den größten Wert heraussuchen soll.  

Im 2. Argument von MAXX erzeugen Sie für jede Zeile der gefilterten Tabelle die Summe aus den Spalten Tag und Ende. MAXX sucht sich aus diesen Ausdrücken den größten Wert heraus. Damit haben Sie dann das Arbeitsende des letzten Arbeitstages ermittelt.

Ermitteln Sie den Beginn des aktuellen Arbeitstages

VAR __Beginn_aktueller_Arbeitstag =
MINX(
    FILTER(
        ALL(Anwesenheitsperioden),
        Anwesenheitsperioden[Person] = SELECTEDVALUE(Anwesenheitsperioden[Person]) &&
        Anwesenheitsperioden[Tag] = MAX(Kalender[Datum])
    ),
    Anwesenheitsperioden[Tag] + Anwesenheitsperioden[Beginn]
)

Hier können Sie einen ähnlichen Ausdruck verwenden. Statt MAXX nutzen Sie jedoch MINX, um den kleinsten Wert aus Tag +  Beginn zu ermitteln. In der FILTER-Funktionen müssen Sie bei der Filterung auf den Tag statt dem Operator kleiner als (<) das Gleichheitszeichen (=) verwenden.

Berechnen Sie die Differenz zwischen Beginn und Ende

VAR __Differenz =
VALUE(__Beginn_aktueller_Arbeitstag - __Ende_letzter_Arbeitstag)

Anschließend ziehen Sie vom Beginn das Ende ab. Verwenden Sie die Funktion VALUE, damit das Ergebnis dieser Subtraktion eine Zahl wird.

Legen Sie den Ausgabetext fest

VAR __Ausgabetext =
IF(__Differenz < 0.4583, "Ruhezeit nicht eingehalten!", "ok")

11 Stunden entsprechen ungefähr den Wert 0,4583. Mithilfe der Funktion IF können Sie nun prüfen, ob die Differenz kleiner ist als diese Zahl. Wenn ja, geben Sie den Text „Ruhezeiten nicht eingehalten!“ aus. Ansonsten soll Ihr Measure ein „ok“ liefern.

Legen Sie fest, dass das Measure die Ruhezeiten nur im richtigen Filterkontext prüft

VAR __Ergebnis =
IF(
    ISBLANK(MAX(Anwesenheitsperioden[Tag])) ||
    NOT(HASONEVALUE(Anwesenheitsperioden[Person])), 
    BLANK(), 
    __Ausgabetext
)

Wenn Sie die Variable  __Ausgabetext als Ergebnis des Measures verwenden, dann würden Sie für jedes Kalenderdatum (die Kalendertabelle beginnt im Jahr 2010!) eine Ausgabe erhalten. Außerdem stünde auch in der Gesamzeile der Visualisierung ein Ergebnis, das ebenfalls wenig sinnvoll wäre. Sie müssen nun also noch sicherstellen, dass eine Ausgabe nur dann erfolgt, wenn:

• das Datum einem Tag in den Anwesenheitsperioden zugeordnet ist
• die Berechnung nicht in der Gesamtzeile erfolgt

Mit dem Ausdruck ISBLANK(MAX(Anwesenheitsperioden[Tag])) prüfen Sie, ob es für das jeweilige Kalenderdatum überhaupt einen zugeordneten Tag in den Anwesenheitsperioden gibt. Diesen Ausdruck müssen Sie mit der vorherigen Prüfung logisch Oder-verknüpfen. Als Oder-Zeichen nutzen Sie die beiden Längsstriche (||). Die Funktion ISBLANK liefert True, wenn kein zugeorndeter Tag in den Anwesenheitsperioden existiert, ansonsten False. Diese Rückgabewerte nutzen Sie in der IF-Funktion, um im Falle von True einen Leerwert auszugeben (dafür nutzen Sie die Funktion BLANK). Im Falle von False geben Sie stattdessen den Ausgabetext aus.

Um die Ausgabe in der Gesamt-Zeile der Visualisierung zu unterdrücken, fügen Sie noch den Ausdruck NOT(HASONEVALUE(Anwesenheitsperioden[Person])) hinzu. Die Funktion HASONEVALUE prüft, ob im aktuellen Filterkontext nur ein einziger Wert für die Spalte Person sichtbar ist. Ist dies der Fall, dann gibt sie True zurück, ansonsten False. Da Sie für die IF-Funktion diese Werte genau umgekehrt benötigen, können Sie mit der Funktion NOT aus jedem True ein False und aus jedem False ein True machen.

Des Weiteren sollten Sie noch sicherstellen, dass das Measure keine fehlerhaften Werte zeigt, wenn der Benutzer im Bericht statt dem Kalenderdatum das Feld Tag aus den Anwesenheitsperioden verwendet. Hier würde nämlich ein unültiger Filterkontext entstehen, der zu diesen fehlerhaften Werten führen würde:

Um dies zu verhindern, erweitern Sie die Prüfung in der IF-Funktion noch um den Ausdruck NOT(HASONEVALUE(Kalender[Datum])). Wenn kein Wert für das Kalenderdatum existiert, gibt der Ausdruck True zurück. Dies führt dann zur Ausgabe von BLANK.

Hinweise

Sie können im Bericht eine vollständige Tabelle mit den Feldern Person, Tag, Beginn und Ende erzeugen:

Sie können jedoch auch eine kompaktere Darstellung wählen, indem Sie die Felder Beginn und Ende weglassen:

Die Beispieldatei können Sie hier kostenlos herunterladen:

Sie möchten Power BI in Ihrem Unternehmen einführen, um Zeit zu sparen und Aufgaben noch effizienter zu erledigen?
In meinem Seminar Power BI - Perfekt für Einsteiger lernen Sie in 5 einfachen Schritten, wie Sie Power BI gewinnbringend einführen können!

Fazit

Als Verantwortlicher im Personalbereich möchten Sie prüfen, ob Ihre Mitarbeitenden die vorgeschriebenen Ruhezeiten einhalten. Dies händisch in Excel zu prüfen kann eine zeitraubende und ermüdende Tätigkeit sein. Ein Measure in Power BI Desktop nimmt Ihnen diese lästige Aufgabe ab und ermittelt zuverlässig, ob jede Mitarbeiterin und jeder Mitarbeiter auf die Ruhezeiten geachtet hat!