Wir haben uns in einem früheren Blogbeitrag bereits mit der Altersberechnung in DAX beschäftigt, damals ging es aber lediglich um die Formel zur Ermittlung des Alters selbst. In der vorliegenden 3-teiligen Blogbeitragsserie geht es jetzt um die darauf aufbauende Ermittlung des Durchschnittsalters der zu einem definierten Zeitpunkt aktiven Mitarbeiter. Dabei wird zuerst vereinfachend der Berechnungsstichtag statisch festgelegt (Teil 1), in weiterer Folge muss der Berechnungsstichtag dynamisch selektiert werden können und auch Zeitreihenauswertungen ermöglicht werden (Teil 2 und Teil 3).

Vielen Dank an die Teilnehmer des diesjährigen Power BI Camps, die diesen Praxisfall intensiv und mit Begeisterung diskutiert haben und denen ich die Lösung der dynamischen Berechnung versprochen habe und dieses Versprechen hiermit einlöse. :)

1. Ausgangssituation

Wir haben eine Anwendung für das HR Reporting eines fiktiven Unternehmens, dabei werden Headcounts sowie Ein- und Austritte analysefähig in Power BI visualisiert. Bitte beachten Sie, daß die Anzahl der aktiven Mitarbeiter aktuell 107 beträgt:

Das zugrundeliegende Datenmodell ist ein simples Star Schema mit einer Dimensionstabelle für die Mitarbeiter ...

... welche die Stammdaten aller jemals im Unternehmen beschäftigten Mitarbeiter (= 253 Personen) inklusive Geburtsdatum enthält:

Die Faktentabelle enthält für jeden Mitarbeiter zumindest einen Eintritt-Datensatz und je einen monatlichen Aktiv-Datensatz für den Zeitraum, in dem der/die MitarbeiterIn im Unternehmen beschäftigt war. Ist der/die MitarbeiterIn ausgetreten, gibt es auch einen Austritt-Datensatz und dann ab diesem Zeitpunkt natürlich keine Aktiv-Datensätze mehr. Die Faktentabelle ist in dieser Anwendung bis 30.04.2019 befüllt, das heißt daß alle späteren Zeitpunkte "Zukunft" darstellen.

Bitte beachten Sie, daß die Aufgabenstellung sehr einfach zu lösen wäre, wenn neben den monatlichen Aktiv-Datensätzen auch monatliche Alter-Datensätze für jeden Mitarbeiter in der Faktentabelle (aus dem Quellsystem) bereitgestellt würden oder wir die Altersberechnung einfach auf den Aktiv-Datensätzen durchführen würden. Die Fragestellung soll aber auf Basis der Stammdaten der Mitarbeiterdimension gelöst werden.

2. Statische Ermittlung des Alters pro Mitarbeiter mit einer Calculated Column

Wie bereits im früheren Blogbeitrag gezeigt, ist es naheliegend und intuitiv, in der Mitarbeiterdimension eine Calculated Column zur Berechnung des Alters anzulegen. Um den statisch definierten Berechnungsstichtag einfach visualisieren zu können und auch leichter wartbar zu machen, wird im ersten Schritt ein Measure mit dem statischen Berechnungsstichtag angelegt:

Berechnung statisch per = DATE(2019;4;30)

Jetzt wird die Calculated Column für die Berechnung des Alters zu diesem Stichtag angelegt. Vereinfachend wenden wir hier die Näherungsmethode mit der Division des Alters in Tagen durch 365,25 an:

Alter (statisch) = ROUNDDOWN(([Berechnung statisch per] - [Geburtsdatum])/365,25;0)

Dazu legen wir noch eine zweite Calculated Column mit den Altersgruppen an:

Altersgruppe (statisch) = 
    ROUNDDOWN([Alter (statisch)]/10;0) & "0 bis " 
    & ROUNDDOWN([Alter (statisch)]/10;0) & "9 Jahre"

3. Berechnung des Durchschnittsalters mit einem Measure

Die Ermittlung des Durchschnittsalters muß jedenfalls mit einem Measure erfolgen, da nur auf diese Weise eine von der aktuellen Filtersituation abhängige gewichtete Durchschnittsbildung möglich ist. Die ungefilterte Berechnung über alle Mitarbeiter der Dimensionstabelle ist sehr einfach mit der AVERAGE()-Aggregatfunktion zu realisieren:

Ф Alter (statisch ungefiltert) = 
        AVERAGE('Dimension Mitarbeiter'[Alter (statisch)])

Um die Durchschnittsbildung transparent zu machen, erstellen wir ein weiteres Measure mit der Anzahl der zugrundeliegenden Mitarbeiter:

Anzahl MA (statisch ungefiltert) = 
        COUNTROWS('Dimension Mitarbeiter')

Die Berechnung funktioniert. Jedoch ist sie nicht praxisrelevant, da das Durchschnittsalter aller 253 jemals im Unternehmen beschäftigten Mitarbeitern zum Stichtag 30.04.2019 ermittelt wird. Die meisten davon sind aber längst ausgetreten (die Anzahl der aktiven Mitarbeiter beträgt 107, siehe oben):

Daher muß die Mitarbeiterdimensionstabelle auf die zum Berechnungszeitpunkt aktiven Mitarbeiter gefiltert werden. Die CALCULATE()-Funktion bietet die Möglichkeit, einer Aggregationfunktion wie AVERAGE() Filterbedingungen mitzugeben. Es gibt sicherlich mehrere Lösungsmöglichkeiten für die Formulierung der Filter, ich habe mich für einen Filter auf die verknüpfte Faktentabelle entschieden. Dieser Filter wirkt aber nur, wenn die Beziehung zwischen der Faktentabelle und der Mitarbeiterdimension bidirektional eingestellt ist, da sonst die Filter nur von der Dimension zur Faktentabelle und nicht andersherum wirken. Die Beziehung im Datenmodell ist aber unidirektional eingestellt und diese soll nicht dauerhaft auf bidirektional umgestellt werden. Daher wird mit der CROSSFILTER()-Funktion die Beziehung virtuell für diese konkrete Berechnung auf bidirektional ("both") gestellt:

Ф Alter (statisch gefiltert) = 
VAR varBerechnungsdatum = [Berechnung statisch per]
RETURN 
    CALCULATE(
        AVERAGE('Dimension Mitarbeiter'[Alter (statisch)]);
        Faktentabelle[Datum] = varBerechnungsdatum;
        Faktentabelle[Kennzahl] = "Aktiv";
        CROSSFILTER(Faktentabelle[MA Nummer];'Dimension Mitarbeiter'[MA Nummer];Both)
    )

Nach der exakt gleichen Methode wird auch das Measure für die Anzahl der Mitarbeiter jetzt mit dem Filter auf die aktiven Mitarbeiter ermittelt:

Anzahl MA (statisch gefiltert) = 
VAR varBerechnungsdatum = [Berechnung statisch per]
RETURN 
    CALCULATE(
        COUNTROWS('Dimension Mitarbeiter');
        Faktentabelle[Datum] = varBerechnungsdatum;
        Faktentabelle[Kennzahl] = "Aktiv";
        CROSSFILTER(Faktentabelle[MA Nummer];'Dimension Mitarbeiter'[MA Nummer];Both)
    )

Das Alter wird jetzt nur noch für jene Mitarbeiter ermittelt, die zum Berechnungsstichtag einen "Aktiv"-Datensatz in der Faktentabelle haben. Damit wird die DS-Altersberechnung sinnvoll:

Zusammenfassende Visualisierung

Mit diesen beiden Measures kann sowohl die Alterspyramide als auch das Durschschnittsalter anschaulich visualisiert werden:

In Teil 2 und Teil 3 der Blogbeitragsserie geht es um die Frage, wie der Berechnungsstichtag für die Altersberechnung durch den User dynamisch selektiert werden kann und wie damit gleichzeitig Zeitreihenauswertung für das Durchschnittsalter möglich werden.