如何计算上月有效餐厅ID数量？PowerBI DAX计算求助

数据模型与需求说明 #

• 表结构：Legal表包含Restaurant ID、Opening date、Closing字段；Calendar表与Legal表存在两个非活动关系（分别关联Calendar[Date]和Legal[Opening date]、Legal[Closing]）
• 开放状态判定规则：
  • 开放：餐厅有开业日期且无停业日期，或停业日期早于分析时间
  • 停业：停业日期晚于分析时间
• 最终需求：在表格中展示每月总开放ID数及上月总开放ID数，示例格式如下：

当前使用的DAX公式 #

Opening 度量值 #

Opening = 
    CALCULATE(
        COUNT(Legal[Restaurant ID]),
        Legal[Opening date] <> BLANK(),
        USERELATIONSHIP('Calendar'[Date],Legal[Opening date]),
        FILTER('Calendar','Calendar'[Date] <= TODAY()),
        FILTER(ALL(Legal),Legal[Opening date] = PREVIOUSMONTH('Calendar'[Date]))
)

Closing 度量值 #

Closing = 
    CALCULATE(
        COUNT(Legal[Restaurant ID]),
        USERELATIONSHIP('Calendar'[Date],Legal[Closing]),
        FILTER('Calendar','Calendar'[Date] <= TODAY())
)

Net 度量值 #

Net = [Opening] - [Closing]

TotalOpen 度量值 #

TotalOpen = 
VAR MaxCalendar = MAX(Calendar[Date])
VAR MaxSalesMonth = CALCULATE(MAX('Calendar'[Month]), ALL('Calendar'), 'Calendar'[Date] = MaxCalendar)
VAR MinCalendar = CALCULATE(MIN(Legal[Opening date]), REMOVEFILTERS('Calendar'))
VAR Restaurants =
    CALCULATE([Net],
        DATESBETWEEN('Calendar'[Date], MinCalendar, MaxCalendar),
        USERELATIONSHIP(Legal[Restaurant ID], 'Restaurants Dim'[Restaurant ID])
    )
RETURN
    IF(MAX('Calendar'[Month]) = MaxSalesMonth, Restaurants, BLANK())

遇到的问题 #

曾尝试使用FILTER(ALL(Legal), Legal[Opening date] = PREVIOUS(Legal[Opening date]))计算上月开业ID数量未成功，核心需求是实现上月总开放ID数的计算。

解决方案 #

步骤1：重新定义TotalOpen度量值 #

原逻辑通过累计开业/停业数计算开放数容易出错，直接基于开放状态判定逻辑计算更准确：

TotalOpen = 
VAR CurrentMonthEnd = EOMONTH(MAX('Calendar'[Date]), 0)
RETURN
CALCULATE(
    DISTINCTCOUNT(Legal[Restaurant ID]),
    Legal[Opening date] <= CurrentMonthEnd,
    OR(
        Legal[Closing] = BLANK(),
        Legal[Closing] > CurrentMonthEnd
    ),
    Legal[Opening date] <> BLANK()
)

该度量值直接计算当前筛选月份月末时的开放餐厅数量：

• Legal[Opening date] <= CurrentMonthEnd：开业日期不晚于当月月末
• OR(Legal[Closing] = BLANK(), Legal[Closing] > CurrentMonthEnd)：无停业日期，或停业日期晚于当月月末（当月仍开放）

步骤2：计算上月总开放ID数 #

利用PREVIOUSMONTH切换上下文到上月，调用已定义的TotalOpen即可：

TotalOpen Previous Month = 
VAR CurrentMonth = MAX('Calendar'[Month])
VAR MinMonth = CALCULATE(MIN('Calendar'[Month]), ALL('Calendar'))
RETURN
IF(
    CurrentMonth = MinMonth,
    BLANK(), -- 第一个月无上月数据，返回空值
    CALCULATE([TotalOpen], PREVIOUSMONTH('Calendar'[Date]))
)

关键说明 #

• 放弃原累计开业/停业的计算方式，直接基于状态判定逻辑计算，性能和准确性更优
• 依赖完整的Calendar日期表确保时间智能函数正常工作
• 通过上下文切换实现上月数据的获取，无需复杂筛选逻辑

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