求DAX度量值：Excel数据模型下产品月度价格变动计算

在Excel数据模型中处理交易数据时，产品价格每月或每隔2-3个月会涨跌。现有DAX度量值无法得到预期结果，需要编写新的度量值实现：当月价格较上月无变动则返回0，有变动则返回差价。

数据样例 #

Dimkey  Partlist    Index   End of Month    Price   Currency    Price Change    required result
33299   Product0    0   2018-01-31  1.0839  USD 0.0016  0
33301   Product0    0   2018-02-28  1.0839  USD 0.0016  0
33297   Product0    0   2018-03-31  1.0839  USD 0.0016  0
33294   Product0    0   2018-04-30  1.0839  USD 0.0016  0
33295   Product0    0   2018-05-31  1.0839  USD 0.0016  0
33293   Product0    0   2018-06-30  1.0839  USD 0.0016  0
33296   Product0    0   2018-07-31  1.0839  USD 0.0016  0
33292   Product0    0   2018-08-31  1.0855  USD 0.0016  0.0016
33302   Product0    0   2018-09-30  1.0855  USD 0.0016  0
33300   Product0    0   2018-10-31  1.0855  USD 0.0016  0
33303   Product0    0   2018-11-30  1.0855  USD 0.0016  0
33298   Product0    0   2018-12-31  1.0855  USD 0.0016  0
31746   Product1    1   2018-01-31  7.96    USD 0.36    0
31745   Product1    1   2018-02-28  7.96    USD 0.36    0
31748   Product1    1   2018-03-31  7.96    USD 0.36    0
31752   Product1    1   2018-04-30  8.06    USD 0.36    0.1
31751   Product1    1   2018-05-31  8.06    USD 0.36    0
31754   Product1    1   2018-06-30  8.32    USD 0.36    0.26
31747   Product1    1   2018-07-31  8.32    USD 0.36    0
31744   Product1    1   2018-08-31  8.32    USD 0.36    0
31753   Product1    1   2018-09-30  8.32    USD 0.36    0
31743   Product1    1   2018-10-31  8.24    USD 0.36    -0.08
31750   Product1    1   2018-11-30  8.24    USD 0.36    0
31749   Product1    1   2018-12-31  8.09    USD 0.36    -0.15

当前DAX度量值 #

Monthly Price Change:=VAR MaxDate = MAX(PriceChange[End of Month])
VAR MinDate = MIN(PriceChange[End of Month])
VAR MaxPrice = CALCULATE(MAX(PriceChange[PO Price]), ALLEXCEPT(PriceChange, PriceChange[Partlist]))
VAR MinPrice = CALCULATE(MIN(PriceChange[PO Price]), ALLEXCEPT(PriceChange, PriceChange[Partlist]))
RETURN
IF(ISBLANK(MaxPrice) || ISBLANK(MinPrice), BLANK(), MaxPrice - MinPrice)

预期结果 #

仅在价格变动的月份显示差价，其余月份返回0。

当前度量值的问题在于用ALLEXCEPT忽略了月份筛选，直接取了产品的最大最小价格差，无法实现逐月对比。以下是符合需求的DAX度量值：

Monthly Price Change := 
VAR CurrentMonthDate = MAX(PriceChange[End of Month])
VAR CurrentPrice = MAX(PriceChange[Price])
VAR PreviousMonthDate = EOMONTH(CurrentMonthDate, -1)
VAR PreviousPrice = 
    CALCULATE(
        MAX(PriceChange[Price]),
        FILTER(
            ALLEXCEPT(PriceChange, PriceChange[Partlist]),
            PriceChange[End of Month] = PreviousMonthDate
        )
    )
RETURN
    IF(
        ISBLANK(PreviousPrice),
        0,
        IF(CurrentPrice = PreviousPrice, 0, CurrentPrice - PreviousPrice)
    )

代码说明 #

1. CurrentMonthDate：获取当前上下文的月末日期
2. CurrentPrice：获取当前月份对应产品的价格
3. PreviousMonthDate：用EOMONTH计算上个月的月末日期，确保和数据中的日期格式匹配
4. PreviousPrice：在相同产品的范围内，筛选出上个月的价格
5. 返回逻辑：如果是第一个月（无上月数据）返回0；如果当月和上月价格相同返回0；否则返回两者的差价

验证结果 #

将该度量值添加到表格后，会完全匹配数据样例中的required result列：

• 价格未变动的月份返回0
• 价格变动的月份显示准确差价（上涨为正，下跌为负）

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