Wie im ersten Teil dieser 3-teiligen Blogbeitragsserie zu sehen ist, ist die Berechnung des Alters zu einem definierten Stichtag auf Basis einer Calculated Column einfach und intuitiv möglich. Im zweiten Teil geht es jetzt um die Frage, ob und wie der Stichtag für die Berechnung des Alters auf Basis einer Calculated Column dynamisiert werden kann. Der Titel lässt es bereits erahnen - es wird beim Versuch bleiben. Ich halte es aber für sehr lehrreich zu sehen, daß die Dynamisierung in diesem Setting wirklich nicht möglich ist. Im dritten Teil wird es dann natürlich eine Lösung geben.

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. :)

Versuch 1: Manuelle Änderung des Parameter-Measures

Wird der im Parameter Measure hinterlegte Berechnungsstichtag geändert ...

... so ändert sich sowohl die Filterung in den beiden Measures als auch die Berechnung in der Calculated Column für das Alter selbst. Die per 30.04.20218 ausgewiesenen Mitarbeiter sind um 1 Jahr jünger als in der Auswertung per 30.04.2019 in der Abbildung oben:

Die Berechnung ist richtig! Problem gelöst - alles super, oder? Leider nein: das DAX Statement im Measure kann nur in Power BI Desktop durch den Power User geändert werden. Ein End User im Power BI Service oder in der Power BI Mobilen App kann keine Measure Definitionen ändern. Das ist auch gut so, da das an vielen anderen Stellen zu großen Problemen führen würde.

Versuch 2: Disconnected Table zur dynamischen Selektion des Stichtags

Wir gehen daher einen Schritt weiter und stellen einen Slicer zur Selektion des Stichtags zur Verfügung. Dazu legen wir einen sogenannten Disconnected Table an, auf den ein Slicer erstellt wird und dessen Selektion mittels eines Measure ausgelesen wird. Dieses Measure wird dann in die Calculated Column mit der Altersberechnung verknüpft.

Annahmegemäß sollen nur die Monatsultimos im Slicer zur Auswahl angeboten werden. Am schnellsten ist eine solche Parameter Tabelle mit einem DAX Statement als Calculated Table erzeugt:

Selektion Stichtag = 
ADDCOLUMNS(
    FILTER(
        CALENDAR(
            DATE(2000;1;1);
            DATE(YEAR(TODAY());12;31)
        );
        [Date]= EOMONTH([Date];0)
    );
    "Jahr";YEAR([Date]);
    "Monat";FORMAT([Date];"MMM");
    "Monat (SORT)";MONTH([Date])
)

Hier die fertige Tabelle (in absteigender Sortierung):

Die Tabelle ist aus Modellierungssicht ein Disconnected Table weil es keine vordefinierte Beziehung zu einer anderen Tabelle im Datenmodell gibt:

In dieser Tabelle legen wir das Measure Stichtag Selektiert an. Die DAX-Funktion SELECTEDVALUE() liefert den Stichtag, sofern die Tabelle - bspw. durch eine Slicer Selektion - auf einen einzelnen Wert gefiltert ist. Ist keine Selektion oder eine Mehrfachselektion getroffen, so kommt der Default Wert (hier "30.04.2019") zur Anwendung:

Stichtag Selektiert = 
    SELECTEDVALUE(
        'Selektion Stichtag'[Date];
        DATE(2019;4;30)
    )

Zur Verdeutlichung: der Slicer filtert über das Feld Date die Parameter Tabelle Selektion Stichtag und das Measure Stichtag Selektiert liefert den selektierten Wert oder ansonsten den hinterlegten Default Wert:

Das Measure Stichtag Selektiert wird jetzt in die Altersberechnung in der Calculated Column eingesetzt, um so die Dynamisierung zu erreichen:

Die Selektion des Stichtags 30.04.2019 liefert die bereits validierten Werte aus der statischen Berechnung, es scheint also zu funktionieren:

Die Änderung der Selektion auf den 30.04.2018 (ein Jahr zuvor) führt zu einer Änderung der Mitarbeiterfilterung, da die beiden Measures für die Anzahl der Mitarbeiter als auch das Durschnittsalter sehr wohl auf den geänderten Wert im Measure Stichtag Selektiert reagieren. ABER: die Berechnung des Alters auf Mitarbeiterebene hat sich nicht geändert, die einzelnen Mitarbeiter sind immer noch gleich alt wie am Stichtag ein Jahr später. Lediglich die Durchschnittsbildung hat sich durch die geänderte Mitarbeiterfilterung geändert, nicht aber die Altersberechnung!

Mit welchem Stichtag wurde dann gerechnet? Die Calculated Column für die Altersberechnung kann nicht den zur Laufzeit ermittelten Wert des Measures verarbeiten (und erhält auch keinen Aktualisierungsimpuls bei einer Änderung der Selektion) sondern verwendet den beim Laden/Aktualisieren des Datenmodells vorhandenen Default Wert des Measures:

Wird der Parameter für den Default Wert aus der SELECTEDVALUE()-Funktion entfernt, dann beträgt der Default Wert 0, das entspricht dem Datum 30.12.1899 (und es ergeben sich entsprechend negative Alterswerte):

Fazit

In Power BI existiert keine uns bekannte Möglichkeit, die Berechnung in einer Calculated Column durch eine User Aktion zur Laufzeit zu verändern. Die Berechnung einer Calculated Column findet IMMER beim Laden/Aktualisieren des Datenmodells statt, der End User kann die Berechnung nicht beeinflußen.

Nach dem intensiven aber gescheiterten Versuch, mit einfachen DAX-Mitteln eine Dynamisierung zu erreichen, zeigen wir im Teil 3 dieser Blogbeitragsserie dann die Lösung mit Measures auf Basis von virtuellen Tabellen.