2020/9/7更新：バージョン2のコードを修正しました。
2020/2/11更新：エラー出しや引数のドロップダウン等に対応したバージョンを追加しました。
公式関数にないものの、Power Query内で処理するなら、経過年月が必要になることもあります。
個人的には月数が出したいので、作ってました。
##コード
引数のintervalにはy,m,dの3種類を入れるか、もしくは省略してください。
インターバルを入れない場合は、年・月・日の3種類それぞれのベースでの結果を返す仕様にしてみました。
要らないかな。
※クリックすると開きます。
コード1
(date1 as date,date2 as date,optional interval as text)=>
//check the argument―"interval"
if List.Contains({"y","m","d",null},interval) then
let
Difference =[Year =Date.Year(date2)-Date.Year(date1),
Month =Date.Month(date2)-Date.Month(date1),
day =Date.Day(date2)-Date.Day(date1)],
MonthAdjustment= if Difference[day]<0 then -1 else 0,
Answers =[
d = Duration.Days(date2-date1),
m = Difference[Year]*12+Difference[Month]+MonthAdjustment,
y = Difference[Year]
+(if (Difference[Month]+MonthAdjustment) <0 then -1 else 0)
],
Answer = if interval =null then Answers
else Record.Field(Answers,interval)
in
Answer
else "intervalはy,m,dのいずれかを指定するか、省略してください。"
##メモ
- 「日」単位以下は、Duration関数群があるので、そちらを使いましょう。
- githubなどにもいくつか作例が出ていたはずです。
- DAXの方にはDATEDIFF関数があります。
- プルダウンで引数を選択させられれば最高ですが、そもそもできるかどうか分からなかったので、ifでエラー回避しました。
##追記分
勉強がてら、手を入れました。コードが長いですが、半分くらいは設例等によるものです。
なお、Excelのワークシート同様、想定外の引数が入った場合にはエラー処理するように修正しました。
###コード2（強化版）
fx_DateDiff_PQ
let
BodyOfFunction=
(date1 as date,date2 as date, optional interval as text)=>
let
Difference =[Year =Date.Year(date2)-Date.Year(date1),
Month =Date.Month(date2)-Date.Month(date1),
day =Date.Day(date2)-Date.Day(date1)],
MonthAdjustment= if Difference[day]<0 then -1 else 0,
Answers =[
d = Duration.Days(date2-date1),
m = Difference[Year]*12+Difference[Month]+MonthAdjustment,
y = Difference[Year]
+(if (Difference[Month]+MonthAdjustment) <0 then -1 else 0)
],
Answer = if (date1<=date2) and List.Contains({"y","m","d",null},interval) then
if interval =null then Answers
//2020/9/7修正
else Record.Field(Answers,interval)
else ...
in
Answer,
//3番目の引数に係るドロップダウンリストを定義
IntervalType =type text
meta [Documentation.AllowedValues = {"y","m","d"}],
//関数自体の説明書き,設例
NewFunctionType =type function(date1 as date,date2 as date, optional interval as IntervalType) as any
meta[Documentation.Name ="fx_DateDiff_PQ",
Documentation.LongDescription=
"<code>date1</code>から<code>date2</code>の期間に係るデータを取り出します。"
& "<code>interval</code>を省略した場合は、年月日の3種類すべての結果をレコードで返します。",
Documentation.Examples=
{
[
Description="2019/12/29から2020/2/29の月数を求めます。日の部分が同じため丁度2か月扱いとなります。",
Code ="fx_DateDiff_PQ(#date(2019,12,29),#date(2020,2,29),""m"")",
Result ="2"
],
[
Description="2019/12/29から2020/2/29の期間情報を求めます。<code>interval</code>を省略します。",
Code ="fx_DateDiff_PQ(#date(2019,12,29),#date(2020,2,29))",
Result ="[d=62,m=2,y=0]"
]
}
],
ReplaceType =Value.ReplaceType(BodyOfFunction,NewFunctionType)
in
ReplaceType
###出来上がりの様子
強化版の関数は、詳細エディタに貼るとこんな感じになります。
###注
ドロップダウンはUI上の話だけで、そこに規定してない値も受け入れができてしまいます。
そのため、ドロップダウン処理とは別に、エラーを出すように仕向けています。
エラーメッセージは、また今度ということで。
###参考
Writing documentation for custom M-functions, Part1 | THE SELF-SERVICE-BI BLOG