Power BI中DAX度量计算预留实例摊销成本的筛选异常问题

问题描述 #

在Power BI中开发DAX度量计算Azure预留实例的摊销成本（将一次性预留成本分摊至整个合约周期）时，遇到以下问题：

• 无筛选条件时，「Reserved Instances Daily Amortized Cost」度量结果正确
• 使用「Azure Reserved Instances Contract Key」切片器筛选后，成本无法按日拆分，仅显示22日的数据

原参考文档为Azure官方《How Azure calculates amortized costs》，原DAX代码如下：

原「Reserved Instances Cost」度量 #

Reserved Instances Cost = 
VAR countsrows = COUNTROWS ( 'Fact Partner Center Costs' ) 

VAR measure_cost = 

CALCULATE (
    SUM ( 'Fact Partner Center Costs'[Total Amount] ),
    KEEPFILTERS ( 'Fact Partner Center Costs'[Category] = "Reserved Instances" )
)

return
CALCULATE( 
measure_cost,
FILTER ( 'Fact Partner Center Costs',  countsrows > 0 ), KEEPFILTERS( 'Dim Azure Reserved Instances Contract'[Contract Client Id]<> BLANK () )
)

原「Reserved Instances Daily Amortized Cost」度量 #

Reserved Instances Daily Amortized Cost = 
SUMX (
    'Fact Partner Center Costs',
    VAR ContextDate = MAX ( 'Date'[Date] )
    VAR CostStartDate = CALCULATE ( MIN ( 'Fact Partner Center Costs'[Charge Start] ), ALL ( 'Date' ) )
    VAR CostEndDate = CALCULATE ( MAX ( 'Fact Partner Center Costs'[Charge End Date] ), ALL ( 'Date' ) )
    VAR ReservedInstancesCost =
        CALCULATE (
            SUMX ( VALUES ( 'Date'[Day] ), [Reserved Instances Cost] ),
            ALL ( 'Date' )
        )

    RETURN

    IF (

        ContextDate >= CostStartDate && ContextDate <= CostEndDate,

        DIVIDE ( ReservedInstancesCost, COUNTROWS ( DATESBETWEEN ( 'Date'[Date], CostStartDate, CostEndDate ) ), 0 ),

        BLANK ()

    )

)

问题根源 #

1. 原摊销度量中，ALL('Date')虽清除了日期上下文，但破坏了切片器带来的合约筛选关联，导致周期计算异常
2. 直接迭代Fact Partner Center Costs表，未按合约维度分组计算，筛选后无法正确匹配每个合约的日期周期

修正后的DAX代码 #

优化后的「Reserved Instances Cost」度量 #

简化冗余逻辑，保留必要筛选：

Reserved Instances Cost = 
CALCULATE (
    SUM ( 'Fact Partner Center Costs'[Total Amount] ),
    KEEPFILTERS ( 'Fact Partner Center Costs'[Category] = "Reserved Instances" ),
    KEEPFILTERS ( NOT ISBLANK ( 'Dim Azure Reserved Instances Contract'[Contract Client Id] ) )
)

修正后的「Reserved Instances Daily Amortized Cost」度量 #

按合约维度迭代，保留筛选上下文并正确计算周期：

Reserved Instances Daily Amortized Cost = 
SUMX (
    VALUES ( 'Dim Azure Reserved Instances Contract'[Azure Reserved Instances Contract Key] ),
    VAR CurrentContractStart = CALCULATE ( MIN ( 'Fact Partner Center Costs'[Charge Start] ) )
    VAR CurrentContractEnd = CALCULATE ( MAX ( 'Fact Partner Center Costs'[Charge End Date] ) )
    VAR TotalContractCost = CALCULATE ( [Reserved Instances Cost], ALL ( 'Date' ) )
    VAR DaysInContract = DATEDIFF ( CurrentContractStart, CurrentContractEnd, DAY ) + 1
    VAR DailyAmortized = DIVIDE ( TotalContractCost, DaysInContract, 0 )
    RETURN
    IF (
        MAX ( 'Date'[Date] ) >= CurrentContractStart && MAX ( 'Date'[Date] ) <= CurrentContractEnd,
        DailyAmortized,
        BLANK ()
    )
)

关键优化点 #

• 按合约维度迭代：使用VALUES(合约Key)确保每个筛选后的合约独立计算摊销，保留切片器筛选上下文
• 精准获取合约周期：移除不必要的ALL('Date')，直接通过当前合约上下文获取起止日期
• 简化天数计算：用DATEDIFF直接计算合约总天数（+1包含起止日），避免日期范围函数的上下文冲突
• 日期范围判断：针对每个合约的周期，判断当前日期是否在范围内，返回对应日摊销成本

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