売上の月平均を計算する時に売上がない月も含めて計算する方法は？

困りごと

売上の月平均を計算した時に実績がない月が含まれない時に実績がない月も含めて平均を計算する方法は？

解決策

連続した日付テーブルを予めPower Queryエディターで作成し、この日付テーブルと月平均を計算する売上テーブルとを日付でマージして売上がない月の売上を0にしてから平均を計算する。

詳細

例えば2021年4月から2023年3月までの売上日・店毎の明細テーブル（売上明細テーブル　以下の図）から毎月の売上の平均をカード（図の赤枠）に表示させたいが売上明細テーブルには売上実績が無い月（5月・12月・2月）の明細は含まれていない場合、21年4月から23年3月までの売上の月平均を算出すると分母に実績のない月がカウントされず12でなく9で除算される。
分母を12で除算するには実績がない月の売上を0でレコードを作成する必要がある。
日付テーブルを別途用意し、売上明細テーブルとマージして売上がない月に0をセットして
平均をとることで分母を12で除算できるようになる。
なお日付テーブルをPower QueryエディターでなくDAX関数で作成する（= Power Bi Desktop「新しいテーブル」から作成する）とPower Queryエディターでマージさせることができない。Power Queryエディターで後述のList.Generate関数を使って作成するとマージできるようになる。

作成イメージ

売上明細テーブル

手順

1. Power BI DesktopのPower Queryエディターを起動して日付テーブル(DateTable)を作成する。
   「空のクエリ」を作成して、「詳細エディタ」を起動して以下のコードを貼り付ける。
   連続した日付のテーブルをPower Queryで作成するにはList.Generate関数を使う。
   例では平均を年度（期間は4月~3月）で計算する為列「年度」をカスタム列で追加している。

   DateTable

   let
       StartDate = #date(2021, 4, 1), 
       EndDate = #date( 2023, 3,31 ),
       Dates = List.Generate(
           () => [
                   日付 = StartDate,
                   年 = Date.Year( 日付 ),
                   月 = Date.Month( 日付 ),
                   日 = Date.Day( 日付 )
               ],
           each [日付] <= EndDate,
           each
               [
                   日付 = [日付] + #duration(1 , 0, 0, 0 ),
                   年 = Date.Year( 日付 ),
                   月 = Date.Month( 日付 ),
                   日 = Date.Day( 日付 )
               ]
       ),
       TableFromRecords = Table.FromRecords(Dates),
       変更された型 = Table.TransformColumnTypes(TableFromRecords,{{"日付", type date}, {"年", type text}, {"月", type text}, {"日", type text}}),
       追加されたカスタム = Table.AddColumn(変更された型, "年度", each if([月]="1" or [月]="2" or [月]="3") then Number.ToText(Number.FromText([年]) -1 ) else [年]),
       変更された型1 = Table.TransformColumnTypes(追加されたカスタム,{{"年度", type text}})
   in
       変更された型1
   

   作成された日付テーブル

   

2. 毎月の売上高を集計した売上テーブルを作成する。
   まず売上明細テーブルから店名を削除し、1.で作成したDateTableをマージして売上が無い日の売上高が0の売上テーブルを作成する。
   DateTableとマージする時はDateTableの行をすべて残す（右外部）様に結合してDateTableを展開して売上明細テーブルの売上日列を削除する。

   

   売上高がnullの行を「値の置換」で0に変換する。

   

3. 日付列から月と年の列を作成する。
   日付列を選択してタブ「列の追加」の「日付」-「年」をクリックして「年」列を作成する。
   同じ様にして「月」の列も作成する。

   

4. 「年」と「月」で「グループ化」をして毎月の売上高を集計する。

   

5. 「年」と「月」をスラッシュ(/)でマージする。

   

6. マージした列を日付列に型変換する。

   

7. 売上テーブルとDateTableにリレーションシップを作成する。

8. テーブルに売上月平均を算出するメジャーを作成する。

   売上月平均

   売上月平均 = AVERAGE('売上テーブル'[売上高]) 
   

9. 平均を算出するメジャーを作成する。

   月売上年平均

   月売上年平均 = CALCULATE('売上テーブル'[売上月平均],DATESYTD('売上テーブル'[年月],"3/31"))
   

10. ライサーとメジャー売上年平均を表示するカードを挿入する。