Altersberechnung in DAX und Power Query

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(
    ([Berechnung per] - [Geburtsdatum]) / 365,25;
    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 exakte Ermittlung ist mit einem aufwendigen 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 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/

Share on linkedin
Share on twitter
Share on facebook

Leave a Replay

Über den Autor

Blog auf Feedly abonnieren

Kategorien

Verwandte Beiträge

Kennen Sie schon data1.io?

data1.io ist unsere innovative Cloud Anwendung zum Sammeln, Aggregieren und Teilen von Geschäftsdaten innerhalb und außerhalb des Unternehmens.

Kostenlos zum Newsletter anmelden

Ihre Anfrage

Schicken Sie uns Ihre Fragen und Anregungen!