BIダッシュボードで活用できる日付の処理
０，はじめに
ご覧いただいてありがとうございます😊
年月や年月日は売上・在庫などのレポートやフィルタに頻繁に使用されますが、BIダッシュボードに使用する際に、”文字列化”することが一般的ではないでしょうか。日付を”日付形式”にしておくと意外と処理しにくいのですが、日付関数についての公式説明はほぼ日付形式の扱いになっているので、思うような処理ができないといったお悩みの原因になっています。
日付関数とテキスト処理関数を組み合わせて処理することがポイントになりますので、以下ケース＆ツールごとにご紹介します。
日付の表示の書式は、20240101のような yyyyMMdd形式、2024/01/01や2024-01-01など/や-を入れるケースなどありますが、当ページではyyyyMMddを使用します。
以下をご覧いただいてもお分かりの通り、ツールごとに関数が結構違います。データ活用チームの方は、実際のところいろんなツールを目的やユーザーにより使い分けていると思います。複数のツールを同じ目的で並べて比較することで、それぞれのツール開発の思想も見えてきてなるほどそういう考えで作っているのかということもわかってきて比較するのも面白いです。
＊本音は、全ツール共通にしてほしい、、、と願いますが。
CASE1: 今日の日付をyyyyMMdd形式で表示したい
EXCEL
TEXT関数で書式を指定できます。今日の日付はTODAY()関数です。
TEXT(TODAY(),"yyyyMMdd")
Powerクエリ
Powerクエリは、DateTime型をText変換する関数で書式をオプションとして指定します。今日の日付はDateTime.LocalNow()です。Date型では指定できず、DateTime型になります。
DateTime.ToText(DateTime.LocalNow(),[Format="yyyyMMdd"])
Power BI DAX関数
DAX関数では、FORMAT関数で書式を指定します。今日の日付はTODAY()関数です。
FORMAT(TODAY(),"yyyyMMdd")
Tableau
テキスト変換はSTR、今日の日付はTODAY()です。
Tableauでは日付のデフォルトがyyyy-MM-dd表示になります。これをyyyyMMddにするTEXT関数やFORMAT関数のようなものがないので（2024年11月時点）テキストに入っているハイフンをREPLACEで除いてみました。
REPLACE(STR(TODAY()),"-","")
Alteryx
Alteryxのテキスト変換はTostring、今日の日付はDateTimeToday()です。
Alteryxは基本の日付表記が yyyy-MM-dd または yyyy/MM/dd です。
日付の書式設定は、DateTimeFormat関数やDateTimeParse関数があるのですが、yyyyMMdd形式には対応していないので、yyyy-MM-ddのテキストからハイフンをReplaceで除く処理をいれました。
Replace(Tostring(DateTimeToday()),"-","")
CASE2: 今月の１か月前/1日前を、yyyyMM形式で表示したい
EXCEL
EDATE関数で、指定月数を変えることができます。
＊年の指定は月数で加減算できます。日付を指定する関数はありませんが、TODAY()に+-Nで、日付を変更することができます
1か月前
TEXT(EDATE(TODAY(),-1),"yyyyMMdd")
1日前
TEXT(TODAY()-1,"yyyyMMdd")
Powerクエリ
Powerクエリには、Date.AddMonths/Date.AddDaysがあります。
1か月前
DateTime.ToText(Date.AddMonths(DateTime.LocalNow(),-1),[Format="yyyyMMdd"])
1日前
DateTime.ToText(Date.AddDays(DateTime.LocalNow(),-1),[Format="yyyyMMdd"])
Power BI DAX関数
DAX関数は、EXCELと同じEDATE関数が使えます
1か月前
FORMAT(EDATE(TODAY(),-1),"yyyyMMdd")
1日前
FORMAT(TODAY()-1,"yyyyMMdd")
Tableau
DATEADD関数で年、月、日の加減算が行えます。
以下の例では、DATEADD関数を使うことで、DATETIME型に型が変わってしまうので改めてDATE関数でTIME情報を削っています。
1か月前
REPLACE(STR(DATE(DATEADD('month',-1,TODAY()))),"-","")
1日前
REPLACE(STR(DATE(DATEADD('day',-1,TODAY()))),"-","")
Alteryx
DateTimeAdd関数で年、月、日の加減算が行えます。
以下の例では、DateTimeAdd関数を使うことで、DATETIME型に型が変わってしまうので改めてDateTimeFormat関数でTIME情報を削っています。
1か月前
Replace(Tostring(DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,"month"),"%Y-%m-%d")),"-","")
1日前Replace(Tostring(DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,"day"),"%Y-%m-%d")),"-","")
CASE3: テキスト型の日付に対し、特定の日付区間に該当する行をカウントしたい
EXCEL
次のようなテーブルを用意します。
このテーブルから、2024年2月1日～2024年3月31日の区間に該当する行をカウントします。（答えは6件）
いろいろなやり方があると思いますが、私は以下のようにしてみました。
各日付に対して、判定のカラムを追加しました。
日付が文字列になっているので数値変換し、範囲に該当する場合”1”それ以外を”0”で入力します。
判定カラム
IF(AND(VALUE(対象セル)>=20240201,VALUE(対象セル)<=20240331),1,0)
判定カラムの1の数をカウントまたはSUMをして結果を出す。
Power BI DAX関数
上記のEXCELと同様のテーブルを日付テーブルとして作成しました。
CALCULATE関数を使うことで、特定の条件に合致する対象を集計できます。
CALCULATE（計算,対象条件）という構造になっています。
CALCULATE(count('日付'[日付]),
AND( value('日付'[日付])>=20240201, value('日付'[日付])<=20240331))
Tableau
SUM文の中にIF文を挿入して条件に合致したものを合計します。
Tableauでは文字列を整数変換する場合はINTを使います。
SUM(IF INT([日付 (日付)])>=20240201 AND INT([日付 (日付)])<=20240331 THEN 1 ELSE 0 END)
ELSEの結果をNULLにしてCOUNTしても同じ結果になります。
COUNT(IF INT([日付 (日付)])>=20240201 AND INT([日付 (日付)])<=20240331 THEN 1 ELSE NULL END)
Alteryx
フォーミュラーツールでEXCELと同様に判定カラムを作り、その後集計ツールでSUMします。判定式は以下です。Alteryxの場合は文字列でも大小判定ができます。
IF [日付]>="20240201" AND [日付]<="20240331" THEN 1 ELSE 0 ENDIF
CASE4: テキスト形式の日付を日付形式に変えて、差分日数を計算したい
EXCEL
テキストで入力されている20240101と20240201の差分日数を計算します。
文字列を日付に変更するには、EXCELではTEXT関数が使えます。
以下の書式 ”0000!/00!/00" を入れることで日付に変換できます。
TEXT("20240201","0000!/00!/00")-TEXT("20240101","0000!/00!/00")
Powerクエリ
Powerクエリの書式変換は、Date.FromTextです。
Date.FromText("20240201")-Date.FromText("20240101"))
Power BI DAX関数
DAX関数には、DATEVALUEという関数がありますが、引数に入れられる
テキスト形式が限定されています。"yyyy/MM/dd"や”yyyy-MM-dd"であれば、DATEVALUE("yyyy/MM/dd"）でテキスト型から日付型に変更することができます。
しかし、yyyyMMddは残念ながらDATEVALUE関数ですとエラーになってしまいます。少し面倒ですが、DATE関数を使い日付型へ変更します。yyyyMMddをMID関数で年/月/日に分割しましょう。
DATE(MID("20240201",1,4),MID("20240201",5,2),MID("20240201",7,2))
-DATE(MID("20240101",1,4),MID("20240101",5,2),MID("20240101",7,2))
Tableau
Tableauには、DATEPARSE関数が用意されています。
これにより書式の形のテキストを日付型に変更してくれます。
DATEPARSE("yyyyMMdd","20240201")-DATEPARSE("yyyyMMdd","20240101")
Alteryx
Alteryxにはパースに日時変換ツールがあります。こちらで文字列を日付に変換する際に、入力されるフォーマットを指定して変換することできます。
または、フォーミュラーツールでDateTimeParse関数で文字列を日付に変更し、DateTimeDiffで差分を取得します。引き算では差分が出せません。
DateTimeDiff
(DateTimeParse("20240201","%Y%m%d"),
DateTimeParse("20240101","%Y%m%d"),"days")
最後までご覧いただきありがとうございました。
こんなことを知りたい🤔と思ったら、是非こちらにご記入ください。
個別にご質問に回答させていただいたり、今後の記事のテーマにしたいと思います。