none
Calculated dates between two dates and modify them to the Range RRS feed

  • 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 Bangaaram 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;

    But, I would need something with DAX. Could someone please help?


    Known is a DROP, Unknown is an OCEAN.

    Thursday, February 6, 2020 8:56 PM
  • This thread is quite like what you need :Date range using DAX query in SSRS

    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Saturday, February 15, 2020 6:51 AM