Styjun

I have a fact table with settlement_date, product_id, service_id, location_id, and ticket_id and srv_adjusted_earning columns.

I have determined the DAX query to generate a calculated column that sums the srv_adjusted_earning column over the date range: settlement date and settlement date - 27 days (i.e. a 4 week window) as:

=CALCULATE(
 SUM(factService[SRV_ADJUSTED_EARNING]),
 DATESBETWEEN
 (
 factService[SETTLEMENT_DATE],
 DATEADD(factService[SETTLEMENT_DATE], -27, DAY), 
 factService[SETTLEMENT_DATE]
 ),
 FILTER(factService, factService[PRO_ID] = EARLIER(factService[PRO_ID])),
 FILTER(factService, factService[SER_ID] = EARLIER(factService[SER_ID])),
 FILTER(factService, factService[LOC_ID_SELLING] = 
 EARLIER(factService[LOC_ID_SELLING])),
 FILTER(factService, factService[TIS_ID] = EARLIER(factService[TIS_ID]))
)

I am trying to convert this DAX calculated column to a measure and I tried the following:

blob:=CALCULATE
(
 SUM(factService[SRV_ADJUSTED_EARNING]),
 DATESBETWEEN
 (
 factService[SETTLEMENT_DATE], 
 DATEADD(factService[SETTLEMENT_DATE], -27, DAY), 
 factService[SETTLEMENT_DATE]
 ),
 ALLEXCEPT(factService, factService[PRO_ID]),
 ALLEXCEPT(factService, factService[SER_ID]),
 ALLEXCEPT(factService, factService[LOC_ID_SELLING]),
 ALLEXCEPT(factService, factService[TIS_ID])
)

But I get:
Error: Calculation error in measure 'factService'[blob]: A single value for column 'SETTLEMENT_DATE' in table 'factService' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Anybody know how I fix this?

As the error mentions, the issue is with factService[SETTLEMENT_DATE]. In the measure, there is no row context so that it knows which date you are talking about, so you need to specify it somehow. I'd suggest using a variable along these lines:

blob :=
VAR SettleDate = MAX ( factService[SETTLEMENT_DATE] )
RETURN
 CALCULATE (
 SUM ( factService[SRV_ADJUSTED_EARNING] ),
 DATESBETWEEN (
 factService[SETTLEMENT_DATE],
 SettleDate - 27,
 SettleDate
 ),
 ALLEXCEPT (
 factService,
 factService[PRO_ID],
 factService[SER_ID],
 factService[LOC_ID_SELLING],
 factService[TIS_ID]
 )
 )

Here the variable picks the maximal settlement date in the current filter context. If that's not exactly what you need, adjust the definition accordingly.