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

はじめに

DAXで誕生日から年齢を計算してみたのが下記記事。続いて、同じことをPower Queryを使用して計算してみます。

準備

実験テーブルにbasedayカラムを用意します。

let
    Source = List.Dates(#date(1984,02,05),20,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "baseday"}})
in
    #"Renamed Columns"

本記事執筆は2022年2月13日です。つまり、この日付列を誕生日だとすると、9行目までが38歳と計算され、その後は37歳となるのが期待する結果です。

注意すること

WEBをざっと検索すると、EXCELベースのPower Queryで下記のような数式が多数紹介されています。この数式、使用上の注意があるので簡単に書きます。

Number.RoundDown(Duration.Days(DateTime.Date(DateTime.LocalNow())-[baseday])/365)

1. DateTime.LocalNow() の扱い

Power BIの場合、最後はPower BI Serviceにアップしてレポートを共有することになると思いますが、端末とクラウドのLocaltimeは計算が違います。安全に運用するのであれば、下記のように書き換えるのが推奨です。

Number.RoundDown(Duration.Days(DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),9))-[baseday])/365)

以前、その点について記事も書いているので参考までに。

2_1. ÷365 の考え方

上記の数式は今日から基準日（誕生日）までの日数を計算し、それを365日割り、小数点以下を切り捨てて年齢を求めています。ざっくりとした年齢であればいいのですが、うるう年（leap year）は1年間が366日あるため、正確な計算結果にはなりません。

作成してある[baseday]に計算を適用すると、2/13が境にならず、2/23まで年齢が加算されてしまっています。

2_2. ÷365.25 の考え方

うるう年（leap year）に対応するため、4年間１周期と考え/365.25と書き換えた数式も散見されますが、やはりこちらもずれます。本来であれば、2/13がしきい値になるはずですが、１日ずれました。

※ずれる日付を探るため[baseday]の値を変えています。

Number.RoundDown(Duration.Days(DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),9))-[baseday])/365.25)

完成したもの １つ目

ということで、以上の注意点を踏まえてPower Queryを組み立てていきます。汎用性のあるようにカスタム関数として適用できるかたちで書いています。

DAX ver と同じ考え方で作成。誕生日と今日の日付を、

• 年
• 月
• 日

とバラバラにして、今日の日付 と 今年の誕生日を比較するという方針です。

2022-02-15 追記
修正前バージョンだと、誕生日がうるう年2月29日の人がいるとerrorがでます。年号を今年に変更して比較するロジックなので、今年の2月に29日が存在しない場合はErrorが返されるわけですね。error対策は複数考えられますが、今回は try~otherwise~を使ってエラー処理しています。

修正前
(birthday as date) =>
    let
        today =
            DateTime.Date(
                DateTimeZone.SwitchZone(
                    DateTimeZone.UtcNow(),
                    9
                )
            ),
        birthday_year = Date.Year(birthday),
        birthday_month = Date.Month(birthday),
        birthday_day = Date.Day(birthday),
        today_year = Date.Year(today),
        compare_date =
            #date(
                today_year,
                birthday_month,
                birthday_day
            ),
        Age =
            if today > compare_date then
                today_year - birthday_year
            else
                today_year - birthday_year - 1
    in
        Age

datastudio (birthday as date) => let today = DateTime.Date( DateTimeZone.SwitchZone( DateTimeZone.UtcNow(), 9 ) ), birthday_year = Date.Year(birthday), birthday_month = Date.Month(birthday), birthday_day = Date.Day(birthday), today_year = Date.Year(today), compare_date = try #date( today_year, birthday_month, birthday_day ) otherwise #date(today_year, birthday_month, 28), Age = if today >= compare_date then today_year - birthday_year else today_year - birthday_year - 1 in Age

完成したもの ２つ目

今日の日付と誕生日を数値に変換して引き算をする・・というやり方。こちらも正確な値がでます。IF文を使用していないので、こちらのほうがすっきりしていますね。

スペシャルサンクス @yugoes1021

(birthday as date) =>
    let
        today =
            DateTime.Date(
                DateTimeZone.SwitchZone(
                    DateTimeZone.UtcNow(),
                    9
                )
            ),
        today_num = Int64.From(Date.ToText(today, "yyyymmdd")),
        date_num = Int64.From(Date.ToText(birthday, "yyyymmdd")),
        result = Number.ToText(today_num - date_num)
    in
        Int64.From(Text.Start(result, 2))

2022-02-15 追記
上記計算式のままだと、頭二桁を取得するので100歳以上に対応していません。
修正版は下記を参照してください。

(birthday as date) =>
    let
        today =
            DateTime.Date(
                DateTimeZone.SwitchZone(
                    DateTimeZone.UtcNow(),
                    9
                )
            ),
        today_num = Int64.From(Date.ToText(today, "yyyyMMdd")),
        date_num = Int64.From(Date.ToText(birthday, "yyyyMMdd")),
        result = Number.ToText(today_num - date_num)
    in
        if Text.Length(result) <= 6 then
            Int64.From(Text.Start(result, 2))
        else
            Int64.From(Text.Start(result, 3))

まとめ

誕生日を計算するだけと思ったのが間違いでしたっ！奥が深い！
より良い方法がありましたら教えて下さい～！