修正同日买卖交易场景下的股票余额DAX度量值

问题原因 #

原度量值中，无论当前行是买入还是卖出，均以MAX(fTrans[Date])作为日期筛选条件，导致同一日期的所有买卖交易都会被纳入计算，最终同一日期的交易行余额完全相同，无法区分买入后、卖出前的余额状态。

方案1：基于交易类型调整日期筛选（无需额外列） #

假设同一日期内所有买入交易均发生在卖出交易之前，可通过判断当前行的交易类型，动态调整卖出交易的筛选范围：

Shares Balance = 
VAR CurrentDate = MAX(fTrans[Date])
VAR CurrentTransType = SELECTEDVALUE(fTrans[Transaction])
-- 计算当前日期及之前的所有买入总和
VAR TotalPurchases = 
    CALCULATE(
        SUM(fTrans[Shares]),
        ALLEXCEPT(fTrans, fTrans[Ticker]),
        fTrans[Transaction] = "Purchase",
        fTrans[Date] <= CurrentDate
    )
-- 针对买入行：仅计算当前日期之前的卖出总和；针对卖出行：计算当前日期及之前的卖出总和
VAR TotalSales = 
    CALCULATE(
        SUM(fTrans[Shares]),
        ALLEXCEPT(fTrans, fTrans[Ticker]),
        fTrans[Transaction] = "Sale",
        IF(CurrentTransType = "Purchase", fTrans[Date] < CurrentDate, fTrans[Date] <= CurrentDate)
    )
RETURN
    TotalPurchases - TotalSales

逻辑说明

• 买入交易行：余额 = 累计买入（含当前买入） - 历史卖出（不含当天卖出），模拟卖出发生前的余额状态
• 卖出交易行：余额 = 累计买入（含当天所有买入） - 累计卖出（含当前卖出），模拟卖出完成后的余额状态

方案2：添加交易索引列（精确处理交易顺序） #

如果同一日期存在卖出先于买入的场景，或需要严格按照实际交易顺序计算，可先在Power Query中为交易添加索引列：

1. 在Power Query中选中fTrans表
2. 按Ticker和Date列排序（若有时间戳，可添加时间列一起排序）
3. 添加索引列（从1开始），命名为TransactionIndex

然后使用以下DAX度量值：

Shares Balance = 
VAR CurrentIndex = MAX(fTrans[TransactionIndex])
VAR CurrentTicker = MAX(fTrans[Ticker])
-- 计算当前交易及之前的所有买入总和
VAR TotalPurchases = 
    CALCULATE(
        SUM(fTrans[Shares]),
        fTrans[Ticker] = CurrentTicker,
        fTrans[Transaction] = "Purchase",
        fTrans[TransactionIndex] <= CurrentIndex
    )
-- 计算当前交易之前的所有卖出总和（卖出行需包含当前卖出，因此这里要调整）
VAR TotalSales = 
    CALCULATE(
        SUM(fTrans[Shares]),
        fTrans[Ticker] = CurrentTicker,
        fTrans[Transaction] = "Sale",
        -- 买入行：仅计算当前交易之前的卖出；卖出行：计算当前交易及之前的卖出
        IF(SELECTEDVALUE(fTrans[Transaction]) = "Purchase", 
           fTrans[TransactionIndex] < CurrentIndex, 
           fTrans[TransactionIndex] <= CurrentIndex)
    )
RETURN
    TotalPurchases - TotalSales

逻辑说明

通过TransactionIndex严格锁定交易顺序，确保每一行的余额计算完全遵循实际交易发生的先后顺序，不受日期重复的影响。

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