如何在DAX中结合DATEADD与WEEKDAY解决工作日销售差异计算问题

1. 修改前一日销售额度量值 #

核心逻辑是跳过周末，直接定位到前一个工作日的销售额，替代简单减1天的方式。可以通过WORKDAY函数获取前一个工作日日期，再计算对应销售额：

SalesYesterday = 
VAR CurrentDate = MAX('Shipping and Service Table'[PackingSlipCreatedDate])
VAR PreviousWorkday = WORKDAY(CurrentDate, -1)
RETURN
CALCULATE(
    'Measures - Sales'[TotalDollarSales],
    'Shipping and Service Table'[PackingSlipCreatedDate] = PreviousWorkday
)

如果模型中有专门的日期表（推荐使用），替换为日期表列会让逻辑更稳定：

SalesYesterday = 
VAR CurrentDate = MAX('Calendar'[Date])
VAR PreviousWorkday = WORKDAY(CurrentDate, -1)
RETURN
CALCULATE(
    'Measures - Sales'[TotalDollarSales],
    'Calendar'[Date] = PreviousWorkday
)

2. 保留差异计算逻辑（无需修改） #

调整后SalesYesterday只会返回工作日的有效销售额，原有差异和差异率度量值可直接沿用：

SalesVariance = 'Measures - Sales'[TotalDollarSales] - [SalesYesterday]
SalesVariance% = DIVIDE([SalesVariance], [SalesYesterday])

补充适配场景 #

• 若需排除法定节假日，可给WORKDAY函数传入节假日日期集合：

  PreviousWorkday = WORKDAY(CurrentDate, -1, 'Holidays'[HolidayDate])
  

• 若存在部分工作日无销售的情况，可改为查找最近的有销售记录的工作日：

  SalesYesterday = 
  VAR CurrentDate = MAX('Shipping and Service Table'[PackingSlipCreatedDate])
  VAR PreviousSalesDate = 
      CALCULATE(
          MAX('Shipping and Service Table'[PackingSlipCreatedDate]),
          'Shipping and Service Table'[PackingSlipCreatedDate] < CurrentDate,
          NOT(WEEKDAY('Shipping and Service Table'[PackingSlipCreatedDate], 2) IN {6,7}) // 排除周六周日
      )
  RETURN
  CALCULATE(
      'Measures - Sales'[TotalDollarSales],
      'Shipping and Service Table'[PackingSlipCreatedDate] = PreviousSalesDate
  )
  

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