如何在Power Query中计算5年与10年移动平均线

原始数据表格 #

计算逻辑说明 #

• 5年移动平均：针对同月份，取当前年份往前推5年的所有数据平均值（如April 2005取2000-2004年的April数据），数据不足5年时留空。
• 10年移动平均：针对同月份，取当前年份往前推10年的所有数据平均值（如August 2010取2000-2009年的August数据），数据不足10年时留空。

Power Query实现步骤 #

1. 将数据导入Power Query，确保列名正确：Date.MonthName、Calendar_Year、Total Number。
2. 按Date.MonthName分组，对每个分组内的年份按升序排序。
3. 添加自定义列计算5年移动平均：

   = let
       currentMonth = [Date.MonthName],
       currentYear = [Calendar_Year],
       filteredRows = Table.SelectRows(#"排序后的表", each [Date.MonthName] = currentMonth and [Calendar_Year] >= currentYear - 4 and [Calendar_Year] < currentYear),
       countRows = Table.RowCount(filteredRows)
     in
       if countRows = 5 then Number.Round(List.Average(filteredRows[Total Number]), 0) else null
   

   注：将#"排序后的表"替换为实际排序步骤的名称。
4. 添加自定义列计算10年移动平均：

   = let
       currentMonth = [Date.MonthName],
       currentYear = [Calendar_Year],
       filteredRows = Table.SelectRows(#"排序后的表", each [Date.MonthName] = currentMonth and [Calendar_Year] >= currentYear - 9 and [Calendar_Year] < currentYear),
       countRows = Table.RowCount(filteredRows)
     in
       if countRows = 10 then Number.Round(List.Average(filteredRows[Total Number]), 0) else null
   

5. 调整列顺序匹配原始表格结构，最后加载数据到Power BI。

关键说明 #

• 代码通过currentYear - 4和currentYear < currentYear筛选前5年数据（如2005年对应2000-2004年），仅当数据量刚好为5条时计算平均值，否则返回null对应空单元格。
• Number.Round函数用于将平均值取整，匹配原始表格的整数结果。

内容的提问来源于stack exchange，提问作者SJG