从BigQuery/SQL Server迁移至Power BI：实现月度客户状态统计

你已经在BigQuery和SQL Server里搞定了按月末判断客户状态的统计逻辑，迁移到Power BI其实可以用两种思路来实现，和你之前的逻辑完全对齐：

方案一：用Power Query生成日期维度表 + DAX度量值（推荐，灵活度高） #

这种方式是Power BI里做时间维度统计的标准做法，对应你SQL Server里循环生成月末日期的逻辑。

第一步：生成包含月末日期的日期表 #

1. 在Power BI中点击「数据转换」进入Power Query编辑器
2. 新建空白查询，粘贴以下M代码（默认取过去12个月的月末日期，你可以根据需求调整StartDate）：

let
    StartDate = Date.AddMonths(DateTime.Date(DateTime.LocalNow()), -12), // 起始日期：当前日期往前推12个月
    EndDate = DateTime.Date(DateTime.LocalNow()),
    Dates = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)),
    #"转换为表" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"重命名列" = Table.RenameColumns(#"转换为表",{{"Column1", "Date"}}),
    #"添加月末日期列" = Table.AddColumn(#"重命名列", "月末日期", each Date.EndOfMonth([Date])),
    #"去重月末日期" = Table.Distinct(#"添加月末日期列", {"月末日期"}),
    #"排序日期" = Table.Sort(#"去重月末日期",{{"月末日期", Order.Ascending}})
in
    #"排序日期"

3. 关闭并应用，把这个表命名为「日期表」，如果客户表有日期字段，可以和客户表建立关系，没有也不影响DAX的上下文判断。

第二步：创建DAX度量值对应各状态统计 #

在「建模」选项卡下新建度量值，分别对应Live、OnBoarding、Disabled状态：

Live状态客户数

Clients_Live = 
VAR 当前月末 = MAX('日期表'[月末日期])
RETURN
CALCULATE(
    COUNTROWS('客户表'),
    '客户表'[ChangedToLiveOn] <= 当前月末,
    OR('客户表'[DisabledOn] > 当前月末, ISBLANK('客户表'[DisabledOn]))
)

OnBoarding状态客户数

Clients_OnBoarding = 
VAR 当前月末 = MAX('日期表'[月末日期])
RETURN
CALCULATE(
    COUNTROWS('客户表'),
    '客户表'[StartedOnBoardingDate] <= 当前月末,
    OR('客户表'[ChangedToLiveOn] > 当前月末, ISBLANK('客户表'[ChangedToLiveOn])),
    OR('客户表'[DisabledOn] > 当前月末, ISBLANK('客户表'[DisabledOn]))
)

Disabled状态客户数（补充需求里的第三种状态）

Clients_Disabled = 
VAR 当前月末 = MAX('日期表'[月末日期])
RETURN
CALCULATE(
    COUNTROWS('客户表'),
    '客户表'[DisabledOn] <= 当前月末
)

第三步：可视化 #

把「日期表」里的「月末日期」拖到画布的轴上，再把三个度量值拖到值区域，就能得到每月各状态的客户数了。

方案二：直接用BigQuery SQL生成结果后导入（和原有逻辑完全一致） #

如果你更习惯用SQL处理，Power BI连接BigQuery时可以直接用自定义SQL查询，把你原来的BigQuery逻辑扩展成生成所有月度统计的结果，然后直接导入可视化。

比如用以下SQL（记得替换你的项目和数据集名称）：

WITH month_ends AS (
  -- 生成过去12个月到当前月的所有月末日期
  SELECT 
    DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL m MONTH), MONTH) + INTERVAL 1 MONTH - INTERVAL 1 DAY AS last_day_of_month
  FROM UNNEST(GENERATE_ARRAY(0, 12)) AS m
)
SELECT
  me.last_day_of_month,
  -- Live状态统计，和你原来的BigQuery逻辑一致
  COUNT(CASE WHEN c.ChangedToLiveOn <= me.last_day_of_month AND (c.DisabledOn > me.last_day_of_month OR c.DisabledOn IS NULL) THEN 1 END) AS Clients_Live,
  -- OnBoarding状态统计
  COUNT(CASE WHEN c.StartedOnBoardingDate <= me.last_day_of_month AND (c.ChangedToLiveOn > me.last_day_of_month OR c.ChangedToLiveOn IS NULL) AND (c.DisabledOn > me.last_day_of_month OR c.DisabledOn IS NULL) THEN 1 END) AS Clients_OnBoarding,
  -- Disabled状态统计
  COUNT(CASE WHEN c.DisabledOn <= me.last_day_of_month THEN 1 END) AS Clients_Disabled
FROM month_ends me
CROSS JOIN `your-project.your-dataset.customer_table` c
GROUP BY me.last_day_of_month
ORDER BY me.last_day_of_month DESC

在Power BI连接BigQuery时，选择「高级选项」粘贴这段SQL，导入后直接用生成的字段做可视化就行，不用再写DAX。

两种方案各有优势：方案一适合后续需要灵活调整日期范围、添加其他维度分析的场景；方案二完全复用你已有的SQL逻辑，上手更快。

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