Die Berechnung des Alters in Jahren - beispielsweise von Mitarbeitern, Mitgliedern oder Kunden - in DAX (Datamodel) und M (Power Query) ist unerwartet schwierig. Die dafür vorgesehenen Funktionen liefern bei näherer Betrachtung keine vollständig richtigen Ergebnisse, die Lösung liefert ein aufwendiges IF-THEN-Statement.
Berechnungsvarianten in DAX
Die DATEDIFF-Funktion mit dem YEAR-Parameter verspricht die optimale Lösung, jedoch ist bei näherer Betrachtung zu sehen, daß die Berechnungen fallweise um 1 Jahr verschoben sind (leider nicht nur rund um den Geburtstag):
Alter (DAX DateDiff) = DATEDIFF(
[Geburtsdatum];
[Berechnung per];
YEAR
)
Die Berechnung nach der simplen DIVISION durch 365,25 ist als Näherung in Ordnung, führt aber - rund um den Geburtstag - zu geringfügig ungenauen Berechnungen:
Alter (DAX Division) = ROUNDDOWN(
([Berechnung per] - [Geburtsdatum]) / 365,25;
0)
Die YEARFRAC-Funktion liefert - ähnlich wie die simple Division - rund um den Geburtstag fallweise leicht fehlerhafte Ergebnisse:
Alter (DAX YearFrac) = ROUNDDOWN(
YEARFRAC([Geburtsdatum];[Berechnung per];1);
0)
Hier eine Auswahl der Fehlberechnungen der 3 Lösungsansätze (beachten Sie bitte, daß bei der DIVISION und bei der YEARFRAC-Funktion zur Verdeutlichung der Unschärfen die ROUNDDOWN-Funktion temporär entfernt wurde):
Die korrekte Ermittlung ist immerhin mit einem IF-THEN-Statement möglich:
Hier das DAX-Statement zum Kopieren:
Alter (DAX IF-THEN) =
VAR varAlter =
Year([Berechnung per])
- Year([Geburtsdatum])
-(
IF(
Month([Berechnung per]) < Month([Geburtsdatum]);
1;
IF(
AND(Month([Berechnung per]) = Month([Geburtsdatum]); Day([Berechnung per]) < Day([Geburtsdatum]))
;1;
0)
)
)
RETURN
IF([Geburtsdatum]=BLANK();
BLANK();
IF(varAlter <0;BLANK(); varAlter)
)
Berechnung in Power Query
Das gleiche Statement kann auch in M verwendet werden. Aufgrund der fehlenden Möglichkeit zur Variablendefinition muß jedoch das Ermittlungsstatement in der IF-THEN Bedingung jedoch wiederholt werden, was den Code schwerer lesbar macht:
Hier wieder das vollständige M-Statement zum Kopieren:
if [Geburtsdatum] = null then null else
if
(Date.Year([Berechnung per]) - Date.Year([Geburtsdatum])
- (if Date.Month([Berechnung per]) < Date.Month([Geburtsdatum]) then 1 else
if Date.Month([Berechnung per]) = Date.Month([Geburtsdatum]) and Date.Day([Berechnung per]) < Date.Day([Geburtsdatum]) then 1 else 0)) < 0 then null
else
Date.Year([Berechnung per]) - Date.Year([Geburtsdatum]) -
(if Date.Month([Berechnung per]) < Date.Month([Geburtsdatum]) then 1 else
if Date.Month([Berechnung per]) = Date.Month([Geburtsdatum]) and Date.Day([Berechnung per]) < Date.Day([Geburtsdatum]) then 1 else 0)
Der Vollständigkeit halber sei auch die AGE-Funktion im Power Query Editor erwähnt, die aus einer Datumspalte ...
... das Alter in Tagen und leider nicht das Alter in Jahren extrahiert:
Visualisierung als Alterspyramide
Die Berechnung der Altersgruppe ist jetzt sehr einfach mit einer weiteren Calculated Column möglich:
Altersgruppe =
ROUNDDOWN([Alter]/10;0) & "0 bis "
& ROUNDDOWN([Alter]/10;0) & "9 Jahre"
Quellen
https://www.mrexcel.com/forum/power-bi/987817-power-query-age-birthdate-action-date.html
https://www.sqlbi.com/blog/marco/2018/06/24/correct-calculate-of-age-in-dax-from-birthday/
3 Gedanken zu „Altersberechnung in DAX und Power Query“
Vielen Dank für die ausführlichen Erklärungen! Ich bin noch neu im Umgang mit Power BI Desktop und konnte alles in allem die Punkte nachvollziehen und umsetzten.
Ich bin lediglich am letzten, vermutlich dem einfachsten, Punkt gescheitert, das Alter in Altersgruppen zu transformieren. Eventuell können Sie mir diesen Punkt nochmals eindeutiger aufschlüsseln.
Die Spalte „Altersgruppe“ kann auf mehrere verschiedene Arten aus der Spalte „Alter“ werden. Im Blog hole ich mir die erste Stelle des zweistelligen Alters (23 Jahre -> 2, 45 Jahre -> 4, usw.) über den Trick einer Division durch 10 und Abrunden auf 0 Kommastellen. Diese Zahl wird dann zum Text „20 bis 29 Jahre“ usw. verkettet. Alternativ könnte auch ein geschachteltes WENN-DANN machen oder eine Excel Zusatztabelle erstellen mit 2 Spalten (Alter, Altersgruppe) und diese entweder mittels 1:n Beziehung oder mittels Merge Query an die Mitarbeitertabelle anhängen und so für die Altersvisualisierung nutzbar machen. Ich hoffe, wenn diese Stichworte helfen, die passende Lösung zu finden.
Ganz lieben Dank für die ausführliche Anleitung. Klärt sie doch mein Problem und erspart viel Suche und Aufwand.
Sehr einleuchtend beschrieben!!