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/
