基于7-6财年的MDX去重人员计数及累积去重总和计算问询

Got it, let's tackle this cumulative distinct count problem for your fiscal year report—your initial approach with Sum(PeriodsToDate...) is on the right track but misses a key detail: summing monthly distinct counts doesn't give you a true cumulative unique count (since the same person might appear in multiple months). Here's the proper MDX solution:

Core Issue with Your Initial Code #

Using Sum(PeriodsToDate(...), [Measures].[Person Count]) adds up the distinct counts of each month in the fiscal year-to-date range. This overcounts people who appear in multiple months, which isn't what you need for a cumulative unique total. Instead, we need to calculate the distinct count of all people who've appeared in any month from the start of the fiscal year up to the current month.

Working MDX Code #

WITH 
  -- Your existing monthly distinct count measure
  MEMBER [Measures].[Person Count] AS 
    DISTINCTCOUNT(Visits[Person ID])
  -- Cumulative unique person count for fiscal year-to-date
  MEMBER [Measures].[Cumulative Person Count] AS 
    DISTINCTCOUNT(
      NonEmpty(
        Visits[Person ID],
        PeriodsToDate(
          [Date].[Fiscal Calendar].[Fiscal Year],
          [Date].[Fiscal Calendar].CurrentMember
        )
      )
    )
SELECT 
  {
    [Measures].[Person Count],
    [Measures].[Cumulative Person Count]
  } ON COLUMNS,
  -- Replace with your actual fiscal month members if needed
  [Date].[Fiscal Calendar].[Fiscal Month].Members ON ROWS
FROM [YourCubeName]

Breakdown of the Cumulative Measure #

1. PeriodsToDate(...): This generates a set of all fiscal month members from the start of the current fiscal year up to the current month being evaluated.
2. NonEmpty(Visits[Person ID], ...): Filters the Person ID dimension to only include IDs that have at least one visit in the fiscal year-to-date range we just defined.
3. DISTINCTCOUNT(...): Counts the unique Person IDs from the filtered set—this gives you the true cumulative unique count of people who've visited in any month up to the current fiscal month.

Alternative Approach (For Compatibility) #

If your MDX engine has specific behavior around NonEmpty, you can use this equivalent aggregate-based approach:

WITH 
  MEMBER [Measures].[Person Count] AS 
    DISTINCTCOUNT(Visits[Person ID])
  MEMBER [Measures].[Cumulative Person Count] AS 
    AGGREGATE(
      PeriodsToDate(
        [Date].[Fiscal Calendar].[Fiscal Year],
        [Date].[Fiscal Calendar].CurrentMember
      ),
      DISTINCTCOUNT(Visits[Person ID])
    )
SELECT 
  {[Measures].[Person Count], [Measures].[Cumulative Person Count]} ON COLUMNS,
  [Date].[Fiscal Calendar].[Fiscal Month].Members ON ROWS
FROM [YourCubeName]

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