# Calculated dates between two dates and modify them to the Range

• ### Question

• I have a peculiar requirement where we have start and end dates for a Report. So, when the user selects the start and end dates in the report, they should be able to retrieve all the records if it either falls under start date or end date and correct the dates accordingly later. I would need this accomplished in a DAX Query.

Below is just an example of how the Dataset looks like:

DDL:

``````CREATE TABLE [#Promos]
([ProductNumber]       INT,
[StartDate]           DATE,
[EndDate]             DATE,
[PromoCode]           VARCHAR(6),
[DaysAvailableInWeek] VARCHAR(20)
);``````

DML:

``````INSERT INTO [#Promos] ([ProductNumber], [StartDate], [EndDate], [PromoCode], [DaysAvailableInWeek])
VALUES (6512, '2020-01-11', '2020-01-13', 'ABC123', '1, 3, 5');
INSERT INTO [#Promos] ([ProductNumber], [StartDate], [EndDate], [PromoCode], [DaysAvailableInWeek])
VALUES(6514, '2020-01-12', '2020-01-14', 'ABC123', '4, 6');
INSERT INTO [#Promos] ([ProductNumber], [StartDate], [EndDate], [PromoCode], [DaysAvailableInWeek])
VALUES(2341, '2020-01-11', '2020-01-25', '321XYZ', '1, 2');``````

Thanks!

• Edited by Thursday, February 6, 2020 8:56 PM Updated the requirement
Thursday, February 6, 2020 5:41 PM

### All replies

• Since, I've reached the character limit in first post, I am posting the rest of the part here.

Here is the working query in SQL:

``````    SELECT [ProductNumber],
CASE
WHEN @StartDate BETWEEN [StartDate] AND [EndDate] THEN @StartDate
WHEN @StartDate < [StartDate] THEN [StartDate]
END AS [StartDate],
CASE
WHEN @EndDate BETWEEN [StartDate] AND [EndDate] THEN @EndDate
WHEN @EndDate > [EndDate] THEN [EndDate]
END AS [EndDate],
[PromoCode],
[DaysAvailableInWeek]
FROM [#Promos]
WHERE [StartDate] <= @EndDate
AND [EndDate]   >= @StartDate
ORDER BY 1;``````