1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

Microsoft PowerBI Desktopで、年月をX軸に設定する(日でなく月表示させる)

Last updated at Posted at 2022-05-23

記載日:2022/5/23
PowerBI Desktopで、年月表示させたいのに年月日表示になってしまい困りました。
年月のテーブルを作ることで解決できたので、備忘録として記載します。

目標

グラフのX軸を年月表示にする(年月日表示ではなく)。

解決策

年月テーブルを用意することと、データテーブル(以下例の場合はsalesDaysテーブル)に年月列を追加することで解決できた。
以下で、具体的な手順を記載する。
salesDaysテーブル(日々の売上データ)を年月表示させるのがゴール。
なお、salesDaysテーブルは一番最後に記載する。

作業

0.年月テーブル作成
画面左の切替ボタンの中から”データ”を選択する。

”テーブルツール”タブ->”新しいテーブル”を選択する。

以下式を記述する。DAX関数で、salesDaysの日付から年月を作っている。

MonthTable = var varCalendar = ADDCOLUMNS( CALENDAR(MIN('salesDays'[date]),MAX('salesDays'[date])), "Year", YEAR([Date]), "Year-Month", format([Date], "YYYY-MM")) return SUMMARIZE(varCalendar, [Year],[Year-Month])

作成したテーブルの”Year-Month”列をクリックしてハイライトさせる。
データ型を”日付”、書式を”yyyy年m月”にする。

1.salesDaysテーブルに年月列を追加
画面右に表示されているテーブルから、salesDaysテーブルをクリックしハイライトさせる。
”テーブルツール”タブ->”新しい列”を選択する。
以下式を記述する。
(先ほどのMonthテーブルの”Year-Month”を式を転用しているだけ。)

yearmonth = format([Date], "YYYY-MM")

先ほどと同様に、データ型を”日付”、書式を”yyyy年m月”にする。

1.テーブルのリレーションシップ作成と、グラフ作成
画面左の切替ボタンの中から”レポート”を選択する。
画面右のsalesDaysテーブルを右クリックし、”リレーションシップの管理”を選択する。MonthTableのYear-Monthと、salesDaysのyearmonthとのリレーションシップを作る。
リレーションシップの作り方は別記事に記載しているので、知りたい方はそちらを確認ください。

グラフのX軸にMonthTableのYear-Monthを設定する。グラフの値には、salesDaysのSalesを設定する。
Year-Monthの下矢印をクリックして設定リストを表示させる。Date-Hierarchyにチェックがついている場合は、Year-Monthへ選択を切り替える。
以下のように年月をX軸としたグラフが表示される。

参考にさせて頂いたサイト

・年月テーブルの式について
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Monthly-Calendar-DAX-Month-name-showing-only-January/m-p/1219447

参考

以下にsalesDaysデータを記載する。

Date,Company,Sales
2021/4/1,TAYATO,8830
2021/4/2,TAYATO,8850
2021/4/3,TAYATO,8870
2021/4/4,TAYATO,8890
2021/4/5,TAYATO,8910
2021/4/6,TAYATO,8930
2021/4/7,TAYATO,8950
2021/4/8,TAYATO,8970
2021/4/9,TAYATO,8990
2021/4/10,TAYATO,9010
2021/4/11,TAYATO,9030
2021/4/12,TAYATO,9050
2021/4/13,TAYATO,9070
2021/4/14,TAYATO,9090
2021/4/15,TAYATO,9110
2021/4/16,TAYATO,9130
2021/4/17,TAYATO,9150
2021/4/18,TAYATO,9170
2021/4/19,TAYATO,9190
2021/4/20,TAYATO,9210
2021/4/21,TAYATO,9230
2021/4/22,TAYATO,9250
2021/4/23,TAYATO,9270
2021/4/24,TAYATO,9290
2021/4/25,TAYATO,9310
2021/4/26,TAYATO,9330
2021/4/27,TAYATO,9350
2021/4/28,TAYATO,9370
2021/4/29,TAYATO,9390
2021/4/30,TAYATO,9410
2021/5/1,TAYATO,9430
2021/5/2,TAYATO,9450
2021/5/3,TAYATO,9470
2021/5/4,TAYATO,9490
2021/5/5,TAYATO,9510
2021/5/6,TAYATO,9530
2021/5/7,TAYATO,9550
2021/5/8,TAYATO,9570
2021/5/9,TAYATO,9590
2021/5/10,TAYATO,9610
2021/5/11,TAYATO,9630
2021/5/12,TAYATO,9650
2021/5/13,TAYATO,9670
2021/5/14,TAYATO,9690
2021/5/15,TAYATO,9710
2021/5/16,TAYATO,9730
2021/5/17,TAYATO,9750
2021/5/18,TAYATO,9770
2021/5/19,TAYATO,9790
2021/5/20,TAYATO,9810
2021/5/21,TAYATO,9830
2021/5/22,TAYATO,9850
2021/5/23,TAYATO,9870
2021/5/24,TAYATO,9890
2021/5/25,TAYATO,9910
2021/5/26,TAYATO,9930
2021/5/27,TAYATO,9950
2021/5/28,TAYATO,9970
2021/5/29,TAYATO,9990
2021/5/30,TAYATO,10010
2021/5/31,TAYATO,10030
2021/6/1,TAYATO,10050
2021/6/2,TAYATO,10070
2021/6/3,TAYATO,10090
2021/6/4,TAYATO,10110
2021/6/5,TAYATO,10130
2021/6/6,TAYATO,10150
2021/6/7,TAYATO,10170
2021/6/8,TAYATO,10190
2021/6/9,TAYATO,10210
2021/6/10,TAYATO,10230
2021/6/11,TAYATO,10250
2021/6/12,TAYATO,10270
2021/6/13,TAYATO,10290
2021/6/14,TAYATO,10310
2021/6/15,TAYATO,10330
2021/6/16,TAYATO,10350
2021/6/17,TAYATO,10370
2021/6/18,TAYATO,10390
2021/6/19,TAYATO,10410
2021/6/20,TAYATO,10430
2021/6/21,TAYATO,10450
2021/6/22,TAYATO,10470
2021/6/23,TAYATO,10490
2021/6/24,TAYATO,10510
2021/6/25,TAYATO,10530
2021/6/26,TAYATO,10550
2021/6/27,TAYATO,10570
2021/6/28,TAYATO,10590
2021/6/29,TAYATO,10610
2021/6/30,TAYATO,10630
2021/7/1,TAYATO,10650
2021/7/2,TAYATO,10670
2021/7/3,TAYATO,10690
2021/7/4,TAYATO,10710
2021/7/5,TAYATO,10730
2021/7/6,TAYATO,10750
2021/7/7,TAYATO,10770
2021/7/8,TAYATO,10790
2021/7/9,TAYATO,10810
2021/7/10,TAYATO,10830
2021/7/11,TAYATO,10850
2021/7/12,TAYATO,10870
2021/7/13,TAYATO,10890
2021/7/14,TAYATO,10910
2021/7/15,TAYATO,10930
2021/7/16,TAYATO,10950
2021/7/17,TAYATO,10970
2021/7/18,TAYATO,10990
2021/7/19,TAYATO,11010
2021/7/20,TAYATO,11030
2021/7/21,TAYATO,11050
2021/7/22,TAYATO,11070
2021/7/23,TAYATO,11090
2021/7/24,TAYATO,11110
2021/7/25,TAYATO,11130
2021/7/26,TAYATO,11150
2021/7/27,TAYATO,11170
2021/7/28,TAYATO,11190
2021/7/29,TAYATO,11210
2021/7/30,TAYATO,11230
2021/7/31,TAYATO,11250
2021/8/1,TAYATO,11270
2021/8/2,TAYATO,11290
2021/8/3,TAYATO,11310
2021/8/4,TAYATO,11330
2021/8/5,TAYATO,11350
2021/8/6,TAYATO,11370
2021/8/7,TAYATO,11390
2021/8/8,TAYATO,11410
2021/8/9,TAYATO,11430
2021/8/10,TAYATO,11450
2021/8/11,TAYATO,11470
2021/8/12,TAYATO,11490
2021/8/13,TAYATO,11510
2021/8/14,TAYATO,11530
2021/8/15,TAYATO,11550
2021/8/16,TAYATO,11570
2021/8/17,TAYATO,11590
2021/8/18,TAYATO,11610
2021/8/19,TAYATO,11630
2021/8/20,TAYATO,11650
2021/8/21,TAYATO,11670
2021/8/22,TAYATO,11690
2021/8/23,TAYATO,11710
2021/8/24,TAYATO,11730
2021/8/25,TAYATO,11750
2021/8/26,TAYATO,11770
2021/8/27,TAYATO,11790
2021/8/28,TAYATO,11810
2021/8/29,TAYATO,11830
2021/8/30,TAYATO,11850
2021/8/31,TAYATO,11870
2021/9/1,TAYATO,11890
2021/9/2,TAYATO,11910
2021/9/3,TAYATO,11930
2021/9/4,TAYATO,11950
2021/9/5,TAYATO,11970
2021/9/6,TAYATO,11990
2021/9/7,TAYATO,12010
2021/9/8,TAYATO,12030
2021/9/9,TAYATO,12050
2021/9/10,TAYATO,12070
2021/9/11,TAYATO,12090
2021/9/12,TAYATO,12110
2021/9/13,TAYATO,12130
2021/9/14,TAYATO,12150
2021/9/15,TAYATO,12170
2021/9/16,TAYATO,12190
2021/9/17,TAYATO,12210
2021/9/18,TAYATO,12230
2021/9/19,TAYATO,12250
2021/9/20,TAYATO,12270
2021/9/21,TAYATO,12290
2021/9/22,TAYATO,12310
2021/9/23,TAYATO,12330
2021/9/24,TAYATO,12350
2021/9/25,TAYATO,12370
2021/9/26,TAYATO,12390
2021/9/27,TAYATO,12410
2021/9/28,TAYATO,12430
2021/9/29,TAYATO,12450
2021/9/30,TAYATO,12470
2021/10/1,TAYATO,12490
2021/10/2,TAYATO,12510
2021/10/3,TAYATO,12530
2021/10/4,TAYATO,12550
2021/10/5,TAYATO,12570
2021/10/6,TAYATO,12590
2021/10/7,TAYATO,12610
2021/10/8,TAYATO,12630
2021/10/9,TAYATO,12650
2021/10/10,TAYATO,12670
2021/10/11,TAYATO,12690
2021/10/12,TAYATO,12710
2021/10/13,TAYATO,12730
2021/10/14,TAYATO,12750
2021/10/15,TAYATO,12770
2021/10/16,TAYATO,12790
2021/10/17,TAYATO,12810
2021/10/18,TAYATO,12830
2021/10/19,TAYATO,12850
2021/10/20,TAYATO,12870
2021/10/21,TAYATO,12890
2021/10/22,TAYATO,12910
2021/10/23,TAYATO,12930
2021/10/24,TAYATO,12950
2021/10/25,TAYATO,12970
2021/10/26,TAYATO,12990
2021/10/27,TAYATO,13010
2021/10/28,TAYATO,13030
2021/10/29,TAYATO,13050
2021/10/30,TAYATO,13070
2021/10/31,TAYATO,13090
2021/11/1,TAYATO,13110
2021/11/2,TAYATO,13130
2021/11/3,TAYATO,13150
2021/11/4,TAYATO,13170
2021/11/5,TAYATO,13190
2021/11/6,TAYATO,13210
2021/11/7,TAYATO,13230
2021/11/8,TAYATO,13250
2021/11/9,TAYATO,13270
2021/11/10,TAYATO,13290
2021/11/11,TAYATO,13310
2021/11/12,TAYATO,13330
2021/11/13,TAYATO,13350
2021/11/14,TAYATO,13370
2021/11/15,TAYATO,13390
2021/11/16,TAYATO,13410
2021/11/17,TAYATO,13430
2021/11/18,TAYATO,13450
2021/11/19,TAYATO,13470
2021/11/20,TAYATO,13490
2021/11/21,TAYATO,13510
2021/11/22,TAYATO,13530
2021/11/23,TAYATO,13550
2021/11/24,TAYATO,13570
2021/11/25,TAYATO,13590
2021/11/26,TAYATO,13610
2021/11/27,TAYATO,13630
2021/11/28,TAYATO,13650
2021/11/29,TAYATO,13670
2021/11/30,TAYATO,13690
2021/12/1,TAYATO,13710
2021/12/2,TAYATO,13730
2021/12/3,TAYATO,13750
2021/12/4,TAYATO,13770
2021/12/5,TAYATO,13790
2021/12/6,TAYATO,13810
2021/12/7,TAYATO,13830
2021/12/8,TAYATO,13850
2021/12/9,TAYATO,13870
2021/12/10,TAYATO,13890
2021/12/11,TAYATO,13910
2021/12/12,TAYATO,13930
2021/12/13,TAYATO,13950
2021/12/14,TAYATO,13970
2021/12/15,TAYATO,13990
2021/12/16,TAYATO,14010
2021/12/17,TAYATO,14030
2021/12/18,TAYATO,14050
2021/12/19,TAYATO,14070
2021/12/20,TAYATO,14090
2021/12/21,TAYATO,14110
2021/12/22,TAYATO,14130
2021/12/23,TAYATO,14150
2021/12/24,TAYATO,14170
2021/12/25,TAYATO,14190
2021/12/26,TAYATO,14210
2021/12/27,TAYATO,14230
2021/12/28,TAYATO,14250
2021/12/29,TAYATO,14270
2021/12/30,TAYATO,14290
2021/12/31,TAYATO,14310
2022/1/1,TAYATO,14330
2022/1/2,TAYATO,14350
2022/1/3,TAYATO,14370
2022/1/4,TAYATO,14390
2022/1/5,TAYATO,14410
2022/1/6,TAYATO,14430
2022/1/7,TAYATO,14450
2022/1/8,TAYATO,14470
2022/1/9,TAYATO,14490
2022/1/10,TAYATO,14510
2022/1/11,TAYATO,14530
2022/1/12,TAYATO,14550
2022/1/13,TAYATO,14570
2022/1/14,TAYATO,14590
2022/1/15,TAYATO,14610
2022/1/16,TAYATO,14630
2022/1/17,TAYATO,14650
2022/1/18,TAYATO,14670
2022/1/19,TAYATO,14690
2022/1/20,TAYATO,14710
2022/1/21,TAYATO,14730
2022/1/22,TAYATO,14750
2022/1/23,TAYATO,14770
2022/1/24,TAYATO,14790
2022/1/25,TAYATO,14810
2022/1/26,TAYATO,14830
2022/1/27,TAYATO,14850
2022/1/28,TAYATO,14870
2022/1/29,TAYATO,14890
2022/1/30,TAYATO,14910
2022/1/31,TAYATO,14930
2022/2/1,TAYATO,14950
2022/2/2,TAYATO,14970
2022/2/3,TAYATO,14990
2022/2/4,TAYATO,15010
2022/2/5,TAYATO,15030
2022/2/6,TAYATO,15050
2022/2/7,TAYATO,15070
2022/2/8,TAYATO,15090
2022/2/9,TAYATO,15110
2022/2/10,TAYATO,15130
2022/2/11,TAYATO,15150
2022/2/12,TAYATO,15170
2022/2/13,TAYATO,15190
2022/2/14,TAYATO,15210
2022/2/15,TAYATO,15230
2022/2/16,TAYATO,15250
2022/2/17,TAYATO,15270
2022/2/18,TAYATO,15290
2022/2/19,TAYATO,15310
2022/2/20,TAYATO,15330
2022/2/21,TAYATO,15350
2022/2/22,TAYATO,15370
2022/2/23,TAYATO,15390
2022/2/24,TAYATO,15410
2022/2/25,TAYATO,15430
2022/2/26,TAYATO,15450
2022/2/27,TAYATO,15470
2022/2/28,TAYATO,15490
2022/3/1,TAYATO,15510
2022/3/2,TAYATO,15530
2022/3/3,TAYATO,15550
2022/3/4,TAYATO,15570
2022/3/5,TAYATO,15590
2022/3/6,TAYATO,15610
2022/3/7,TAYATO,15630
2022/3/8,TAYATO,15650
2022/3/9,TAYATO,15670
2022/3/10,TAYATO,15690
2022/3/11,TAYATO,15710
2022/3/12,TAYATO,15730
2022/3/13,TAYATO,15750
2022/3/14,TAYATO,15770
2022/3/15,TAYATO,15790
2022/3/16,TAYATO,15810
2022/3/17,TAYATO,15830
2022/3/18,TAYATO,15850
2022/3/19,TAYATO,15870
2022/3/20,TAYATO,15890
2022/3/21,TAYATO,15910
2022/3/22,TAYATO,15930
2022/3/23,TAYATO,15950
2022/3/24,TAYATO,15970
2022/3/25,TAYATO,15990
2022/3/26,TAYATO,16010
2022/3/27,TAYATO,16030
2022/3/28,TAYATO,16050
2022/3/29,TAYATO,16070
2022/3/30,TAYATO,16090
2022/3/31,TAYATO,16110
1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?