指定された２つの日付の期間を計算したい時。DAX関数なら DATEDIFF関数の出番なんだけど、Power Queryでやるとどうなるのか。
DATEDIFFもどきを作る｜Power Query - Qiita
ここの記事を見ていて自分もカスタム関数で DATEDIFを作ってみたくなった。どうせならバグ（といっていのかどうか未だに不明）にも対応させたい。
DATEDIF関数の問題に「閏年を挟む期間計算で結果がおかしくなる」がある。これについては長年の課題で、Excel2007で一度改善が試みられた節があるけど、失敗して結局元に戻ってしまった。
事例を上げればキリがないけど、例えば開始日が「2022/1/31」で終了日が「2022/2/28」の場合。DATEDIF関数の仕様は「初日不算入」のはずだから本来「YM」や「M」の結果は「1」にならないといけないが、「0」になってしまう。この仕様は場合によって「YM」や「M」以外にも影響する。開始日「2022/11/30」で終了日「2024/12/31」の場合、「YD」の結果が「32」になってしまう。
もっというなら開始日が「2028/1/31」で終了日が「2030/3/2」だと「M」の結果はなんと「-1」になってしまうのだ。これをバグと呼ばずして何とする。
YEARFRAC関数を使っても結局は似たような問題は起きるので、民法（143条）の規定に合わせて正しく計算するのが一番だと思う。
DateDiff関数
(date1 as date, date2 as date, optional interval as text)=>
let
d1 = Date.Day(Date.AddDays(date1,1)),
d2 = Date.Day(Date.AddDays(date2,1)),
year = Date.Year(date2) - Date.Year(date1),
month = Date.Month(date2) - Date.Month(date1),
day = Date.Day(date2) - Date.Day(date1),
adj1 = if d1=1 and d2>1 then 1 else 0,
adj2 = if d1>1
and d2>1
and d2<d1 then 1 else 0,
asst1 =
Number.Mod(
List.Max({Date.Day(date2)-d1+1,0}),
Date.Day(date2)
),
asst2 =
Number.Mod(
Date.Day(date2)
- Date.Day(List.Min({
Date.AddDays(Date.StartOfMonth(
Date.AddMonths(date2,-1)), Date.Day(date1)
),
Date.StartOfMonth(date2)
}))
+ Date.Day(Date.AddDays(date2,-Date.Day(date2))) + 1,
Date.Day(Date.AddDays(date2,-Date.Day(date2)))
),
answers = [
Y = Number.RoundDown(M/12, 0),
YM = Number.Mod(M,12),
M = year*12 + month - adj1 - adj2,
MD = if d2=1 then asst1
else if d1=1 then Date.Day(date2) else asst2,
D = Duration.Days(date2-date1),
YD = Number.Mod(D, List.Max({Y,1})*365)
],
answer =
if date1<=date2 and List.Contains(
{"Y","YM", "M", "MD","YD", "D", null},
interval
) then
if interval =null then answers
else Record.Field(answers, interval)
else ...,
result = try answer otherwise null
in
resultもっといい方法がありそうな気がするけど、ひとまずこれで関数は完成。後は「開始日」と「終了日」列が入力された「日付テーブル」を用意して、そこに列を追加するだけでいい。
Y列の追加 = Table.AddColumn(日付テーブル, "Y",
each DateDiff([開始日], [終了日], "Y"), type number
),
YM列の追加 = Table.AddColumn(Y列の追加, "YM",
each DateDiff([開始日], [終了日], "YM"), type number
),
M列の追加 = Table.AddColumn(YM列の追加, "M",
each DateDiff([開始日], [終了日], "M"), type number
),
MD列の追加 = Table.AddColumn(M列の追加, "MD",
each DateDiff([開始日], [終了日], "MD"), type number
),
YD列の追加 = Table.AddColumn(MD列の追加, "YD",
each DateDiff([開始日], [終了日], "YD"), type number
),
D列の追加 = Table.AddColumn(YD列の追加, "D",
each DateDiff([開始日], [終了日], "D"), type number
)以上。YDの結果がところどころ違うけど、むしろこっちが正しいんじゃないかと個人的には思うのでそのままにしておく。使用頻度皆無だから誰も気にしないだろうけど。
