如何解决滚动12个月内新旧供应商重复统计的MDX问题

嘿，这个问题我之前碰到过类似场景——按月统计没问题，但滚动周期里因为供应商跨状态导致重复计数。核心是得把按月的新旧判断升级为整个滚动12个月窗口的全局判断，而不是每个月单独标记。给你两个实用方案：

方案一：ETL层预处理（推荐，性能更优） #

如果能调整数据链路，优先在ETL阶段提前计算好每个供应商在任意滚动窗口内的状态，这样MDX里直接用就行，性能会好很多：

1. 先抓取每个供应商的「首次新供应商月份」
   从事实表里提取所有供应商第一次满足「注册年月=发票年月」的月份，存到临时表：

   -- 示例SQL（根据你的数据库语法调整）
   SELECT DISTINCT
       [Supplier no],
       SUBSTRING([Invoice date], 1, 6) AS FirstNewMonth
   FROM FactTable
   WHERE SUBSTRING([Reg date], 1, 6) = SUBSTRING([Invoice date], 1, 6)
   

2. 生成月度快照标记
   针对每个月份，生成该月份向前推12个月的窗口，标记每个供应商在这个窗口里是否当过新供应商：

   WITH SupplierFirstNew AS (
       -- 引用上面的查询结果
       SELECT DISTINCT [Supplier no], SUBSTRING([Invoice date],1,6) AS FirstNewMonth FROM FactTable WHERE ...
   )
   SELECT
       t.YearMonth AS CurrentMonth,
       s.[Supplier no],
       CASE 
           WHEN s.FirstNewMonth BETWEEN 
               -- 计算当前月份往前推11个月的年月（比如202010的话，起始是201911）
               FORMAT(DATEADD(MONTH, -11, CAST(CONCAT(t.YearMonth, '01') AS DATE)), 'yyyyMM')
               AND t.YearMonth
           THEN 'New supplier' 
           ELSE 'Old supplier' 
       END AS [Cycle Old/New]
   FROM 
       (SELECT DISTINCT SUBSTRING([Invoice date],1,6) AS YearMonth FROM FactTable) t
   CROSS JOIN SupplierFirstNew s
   

   把这个结果存成维度表或事实表的辅助字段，之后在MDX里直接基于[Cycle Old/New]做distinct count，就不会有重复问题了。

方案二：MDX层面直接计算（适合无法改ETL的情况） #

如果动不了数据，只能在MDX里处理，那就通过计算成员来做全局判断：

首先定义一个基础度量，标记单月是否为新供应商：

MEMBER [Measures].[Is Monthly New] AS
    IIF(
        -- 注意Reg date和Invoice date的引用方式，若为维度属性则用.MemberValue
        SUBSTRING([Supplier].[Reg Date].CurrentMember.MemberValue, 1, 6) 
        = SUBSTRING([D Time].[Year-Month].CurrentMember.MemberValue, 1, 6),
        1,
        0
    )

再定义滚动12个月内的状态判断和统计：

WITH 
-- 单月新供应商标记
MEMBER [Measures].[Is Monthly New] AS
    IIF(
        SUBSTRING([Supplier].[Reg Date].CurrentMember.MemberValue, 1, 6) 
        = SUBSTRING([D Time].[Year-Month].CurrentMember.MemberValue, 1, 6),
        1,
        0
    )
-- 判断当前供应商在滚动12个月窗口内是否当过新供应商
MEMBER [Measures].[Is New in 12M] AS
    IIF(
        EXISTS(
            -- 滚动12个月范围：去年同月的下一个月到当前月
            {ParallelPeriod([D Time].[Year-Month].[Year], 1, [D Time].[Year-Month].CurrentMember).Lead(1) 
             : [D Time].[Year-Month].CurrentMember}
            * {[Supplier].[Supplier no].CurrentMember},
            [Measures].[Is Monthly New]
        ),
        1,
        0
    )
-- 滚动12个月新供应商数量（去重）
MEMBER [Measures].[12M New Supplier Count] AS
    SUM(
        [Supplier].[Supplier no].[Supplier no].Members,
        IIF([Measures].[Is New in 12M] = 1, 1, 0)
    )
-- 滚动12个月旧供应商数量：仅统计从未当过新供应商的
MEMBER [Measures].[12M Old Supplier Count] AS
    SUM(
        [Supplier].[Supplier no].[Supplier no].Members,
        IIF([Measures].[Is New in 12M] = 0, 1, 0)
    )
-- 查询示例
SELECT
    {[Measures].[12M New Supplier Count], [Measures].[12M Old Supplier Count]} ON 0,
    [D Time].[Year-Month].[Month].Members ON 1
FROM YourCube

关键提醒 #

• 性能：MDX里遍历所有供应商成员的操作，若供应商数量很大可能会变慢，所以优先选ETL预处理方案。
• 窗口边界：确认ParallelPeriod(...).Lead(1)的范围是否正确——比如截止到202010的话，窗口是201911到202010，共12个月，这个表达式是准确的。
• 需求匹配：按照你的要求，只要供应商在12个月内有一次匹配就视为周期内新供应商，旧供应商仅统计从未满足新供应商条件的，上面的方案完全贴合这个逻辑。

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