# Calculated measure between two dates • ### Question

• Following fact table

- Amount

- StartDate

- EndDate

- SomeOtherDate

If the user selects a date in the time dimension I want to build the sum of Amount where the selected date is between StartDate and EndDate.

Like this

SELECT SUM(Amount) WHERE <date> between StartDate and EndDate

Is it possible to create a calculated measure like this.

Thanks.

Sunday, September 5, 2010 4:59 PM

• You can use the range operator in your where clause eg WHERE ([Date].[Date].& : [Date].[Date].&)
• Proposed as answer by Tuesday, September 7, 2010 3:08 AM
• Marked as answer by Monday, September 13, 2010 2:39 AM
Sunday, September 5, 2010 10:04 PM

### All replies

• You can use the range operator in your where clause eg WHERE ([Date].[Date].& : [Date].[Date].&)
• Proposed as answer by Tuesday, September 7, 2010 3:08 AM
• Marked as answer by Monday, September 13, 2010 2:39 AM
Sunday, September 5, 2010 10:04 PM
• How can I use such a where expression in a calculated member definition ?
Monday, September 13, 2010 10:02 AM
• Here's an example summing a measure over a particular date range (Adventure Works)

WITH MEMBER [Measures].[Sales Date Range]
AS
'
SUM([Delivery Date].[Date].& : [Delivery Date].[Date].&,[Measures].[Internet Freight Cost])
'

SELECT [Measures].[Sales Date Range] ON 0