[Power BI] 誕生日から現在の年齢を計算する。DAXとJAPAN RUGBY LEAGUE ONE の選手プロフィールで。

はじめに

2022年に開幕したJAPAN RUGBY LEAGUE ONEの公式ホームページから取得したデータを勝手にいじりながら勉強をしています。

やってみたこと

DAXを使用して生年月日から年齢を計算。なかなか難しかった。

本記事での完成形はこちらです。参考にしてください。

Age =
VAR today =
    UTCNOW () + 9 / 24
VAR basedate =
    MAXX ( 'Height&Weight', 'Height&Weight'[Birthday] )
VAR compare_date =
    IF (
        basedate <> BLANK (),
        DATE ( YEAR ( today ), MONTH ( basedate ), DAY ( basedate ) )
    )
VAR hikaku =
    DATEDIFF ( compare_date, today, DAY )
VAR result =
    IF (
        hikaku >= 0,
        DATEDIFF ( basedate, today, YEAR ),
        DATEDIFF ( basedate, today, YEAR ) - 1
    )
RETURN
    IF (
        HASONEVALUE ( 'Height&Weight'[Birthday] ),
        IF ( basedate <> BLANK (), result, BLANK () ),
        BLANK ()
    )

検証スタート

誕生日を取得

公式サイトのプロフィールページを見ると、生年月日は記載があるものの、現年齢がわかりません。

ここから現年齢を計算していきます。

実験準備

※本記事は2022/02/12に書いています。

まずは実験用の日付テーブルを作成します。
Power Query Editor の「空のクエリ」に書きPower Queryを書き、年齢の計算が期待通り行ってくれるかを調べていきます。

= List.Dates(#date(1984,02,05),20,#duration(1,0,0,0))

実験テーブルに [baseday] という列を作成しました。

実験１

まずはDAXのDATEDIFF関数を使用して計算します。
引数のstartdayからenddayの間に年の区切りが何個あるかを数えてくれます。

DATEDIFF(
    MAXX( '実験', '実験'[baseday] ),
    UTCTODAY( ) + 9 / 24,
    YEAR
)

結果１

引数に２つの値を指定しましたが、その間にある年区切りの数なので、すべての行で38という数字が出ています。この数式だと指定日を基準に年を数えているわけではなくなっているため、1月1日誕生日の人しか正しい計算がされません。

実験 ２

そこで基準日（誕生日）を境に年齢が変化するように分岐を作成していきます。
今日と今年の誕生日を比較してすでに誕生日を過ぎているならDATEDIFF関数の求めた値のまま、まだ誕生日前ならDATEDIFF関数の値から１を引きます。

まずは今日を求める。

Power BI Serviceにもアップする可能性があるなら、TODAY関数などを使うとTimezone計算で日付がずれてしまうことも考えられるので、UTCを求めてからタイムゾーンを調整する方法がオススメ。

today = UTCNOW( ) + 9 / 24

今日と比較する日付の作成

単純に基準日（誕生日）を今日と比較すると、西暦が古いため比較ができない。DATE関数を使用して月日は同じで西暦だけ今日の西暦をもってくる。

compare_date = 
VAR today = UTCNOW( ) + 9 / 24
VAR compare_date =
    DATE(
        YEAR( today ),
        MONTH( MAXX( calender, calender[Date] ) ),
        DAY( MAXX( calender, calender[Date] ) )
    )
RETURN
    compare_date

結果２

今日の日付と、年号を揃えた基準日付を用意できました。この２つを比較します。

実験３

実験２で用意した日付データ２つを比較し、基準日よりも大きければ１を引く処理を加えています。比較する際にもDATEDIFF関数を使用。

Age_proto_1 =
VAR today = UTCNOW( ) + 9 / 24
VAR basedate = MAXX( '実験', '実験'[baseday] )
VAR compare_date =
    DATE(
        YEAR( today ),
        MONTH( MAXX( calender, calender[Date] ) ),
        DAY( MAXX( calender, calender[Date] ) )
    )
VAR hikaku = DATEDIFF( compare_date, today, DAY )
VAR result =
    IF(
        hikaku >= 0,
        DATEDIFF( basedate, today, YEAR ),
        DATEDIFF( basedate, today, YEAR ) - 1
    )
RETURN
    result

結果３

おおむね完成しました。今日（2022/02/12）を境に年齢が変わっています。

実験４

あとは細かい微調整です。まずは１行目に表示されているブランク行になにも表示されないようにします。さきほど取得した値を、行コンテキストがBlankではなかったらを判定するIF文で囲います。

Age_proto_2 = 
VAR today = UTCNOW( ) + 9 / 24
VAR basedate = MAXX( '実験', '実験'[baseday] )
VAR compare_date =
    DATE(
        YEAR( today ),
        MONTH( MAXX( calender, calender[Date] ) ),
        DAY( MAXX( calender, calender[Date] ) )
    )
VAR hikaku = DATEDIFF( compare_date, today, DAY )
VAR result =
    IF(
        hikaku >= 0,
        DATEDIFF( basedate, today, YEAR ),
        DATEDIFF( basedate, today, YEAR ) - 1
    )
RETURN
    IF(
        basedate <> BLANK(),
        result
    )

結果４

基準日がBlankになっている行の計算列もBlankになりました。

実験５

合計列が表示されてしまっているのも非表示にしましょう。さらにHASONEVALUE関数で囲います。

Age_proto_3 = 
VAR today =
    UTCNOW () + 9 / 24
VAR basedate =
    MAXX ( 'Height&Weight', 'Height&Weight'[Birthday] )
VAR compare_date =
    IF (
        basedate <> BLANK (),
        DATE ( YEAR ( today ), MONTH ( basedate ), DAY ( basedate ) )
    )
VAR hikaku =
    DATEDIFF ( compare_date, today, DAY )
VAR result =
    IF (
        hikaku >= 0,
        DATEDIFF ( basedate, today, YEAR ),
        DATEDIFF ( basedate, today, YEAR ) - 1
    )
RETURN
    IF (
        HASONEVALUE ( 'Height&Weight'[Birthday] ),
        IF ( basedate <> BLANK (), result, BLANK () ),
        BLANK ()
    )

結果５

合計行の計算結果も消えました。

実際に生年月日列に適用する。

実験を終え、関数の動きは把握できたので、実際に誕生日カラムに適用します。
40歳以上で現役プレイヤーがこんなにたくさん・・！

完成

まとめ

誕生日から年齢を算出するのは簡単かと思いきや、なかなか奥が深かった。もっといい書き方があるんだろうなぁと思い、引き続き勉強を進めようと思った。次回はPower Queryでチャレンジしてみよう。

その後

いろいろな方々にフィードバックいただき、よりシンプルな計算方法を複数教えてもらいました。

一例

Age = INT ( YEARFRAC ( Customer[Birthdate], TODAY (), 1 ) )

参考資料