MDX中SQL Union All的等效实现及拒付率计算问题

需求说明 #

需要创建存储过程/函数生成MDX查询，基于日期范围拆分出自定义年度周期并返回指标。例如查询2021-03-01至2023-02-28的数据时，需拆分为20210301:20220228和20220301:20230228两个周期，返回对应聚合结果。

目前已有多个度量值能按预期格式返回准确数值，但拒付率度量值的计算逻辑无法沿用现有方法。若能在MDX中实现类似SQL UNION ALL合并两个SELECT语句的功能，即可解决问题。以下是逻辑示例代码（仅作演示，无法运行）：

SELECT NON EMPTY {[Measures].[%Denial Rate] } ON COLUMNS
 FROM ( SELECT ( [Date - Remit Received].[Date Key].&[20210101] : [Date - Remit Received].[Date Key].&[20211231] ) ON COLUMNS 
 FROM ( SELECT ( { [Office ID].&[9] } ) ON COLUMNS 
 FROM [Remit])) WHERE ( [Office ID].&[9] ) 

union all 

SELECT NON EMPTY {[Measures].[%Denial Rate] } ON COLUMNS
 FROM ( SELECT ( [Date - Remit Received].[Date Key].&[20220101] : [Date - Remit Received].[Date Key].&[20221231] ) ON COLUMNS 
 FROM ( SELECT ( { [Office ID].&[9] } ) ON COLUMNS 
 FROM [Remit])) WHERE ( [Office ID].&[9] ) 

是否存在这样的实现方式，能返回预期格式的结果？

当前代码（格式符合要求但数值不准确） #

以下是当前使用的代码，它能生成预期格式的结果，但返回的拒付率数值不准确：

with  
           Set Denial_y1 as
           {([Denied Claim].[Denied Claim].&[Yes],[Date - Remit Received].[Date Key].[Date Key].&[20210101]:[Date - Remit Received].[Date Key].[Date Key].&[20211231])}
            
           set ClaimDetail_all1 as
           {([Date - Remit Received].[Date Key].[Date Key].&[20210101]:[Date - Remit Received].[Date Key].[Date Key].&[20211231])}
            
           Member [Measures].[(%Denial Rate_hd,Year 2021)] as
           sum(Denial_y1,Measures.[Distinct Claim Count_hd])/sum(ClaimDetail_all1,Measures.[Distinct Claim Count_hd])
            
        
           Set Denial_y2 as
           {([Denied Claim].[Denied Claim].&[Yes],[Date - Remit Received].[Date Key].[Date Key].&[20220101]:[Date - Remit Received].[Date Key].[Date Key].&[20221231])}
        
          
          set ClaimDetail_all2 as
           {([Date - Remit Received].[Date Key].[Date Key].&[20220101]:[Date - Remit Received].[Date Key].[Date Key].&[20221231])}
          
          Member [Measures].[(%Denial Rate_hd,Year 2022)] as
          sum(Denial_y2,Measures.[Distinct Claim Count_hd])/sum(ClaimDetail_all2 ,Measures.[Distinct Claim Count_hd])
          
          Select {[Measures].[(%Denial Rate_hd,Year 2021)],[Measures].[(%Denial Rate_hd,Year 2022)] } on 0
          from Remit
          where({[Office ID].[9]} )

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