--  DATESBETWEEN returns the dates between the boundaries specified.
--  The boundaries are both included in the result.
--  If EndDate is earlier than StartDate, the result is an empty table.
EVALUATE
VAR StartDate = DATE ( 2008, 08, 25 )
VAR EndDate =   DATE ( 2008, 08, 31 )
RETURN
    DATESBETWEEN ( 'Date'[Date], StartDate, EndDate )
ORDER BY [Date]

--  DATESBETWEEN returns dates that exist in the Date table.
--  A date lower than the minimum makes DATESBETWEEN start
--  from the min value anyway.
--  The content of the Date table starts from Jan 1, 2005.
EVALUATE
VAR StartDate = DATE ( 2004, 01, 01 )   -- Lower than MIN ( 'Date'[Date] )
VAR EndDate =   DATE ( 2005, 01, 6 )
RETURN
    DATESBETWEEN ( 'Date'[Date], StartDate, EndDate )
ORDER BY [Date]

--  With a calendar, DATESBETWEEN returns all the columns of the calendar:
--  the primary column of each category and the time-related columns.
--  Gregorian is a calendar defined on the Date table.
EVALUATE
VAR StartDate = DATE ( 2008, 08, 25 )
VAR EndDate =   DATE ( 2008, 08, 31 )
RETURN
    DATESBETWEEN ( Gregorian, StartDate, EndDate )
ORDER BY 'Date'[Date]

--  With a calendar, a StartDate or EndDate that does not exist
--  in the Day column works like BLANK: DATESBETWEEN uses
--  the first or the last date of the calendar.
--  With a date column, DATESBETWEEN uses the dates as they are.
--  The Date table contains the dates from Jan 1, 2005 to Dec 31, 2011.
EVALUATE
VAR StartWithTime = DATE ( 2008, 08, 25 ) + TIME ( 12, 0, 0 )
VAR EndDate = DATE ( 2008, 08, 31 )
VAR ColumnWithTime = DATESBETWEEN ( 'Date'[Date], StartWithTime, EndDate )
VAR CalendarWithTime = DATESBETWEEN ( Gregorian, StartWithTime, EndDate )
VAR ColumnAfterData = DATESBETWEEN ( 'Date'[Date], DATE ( 2015, 1, 1 ), DATE ( 2015, 12, 31 ) )
VAR CalendarAfterData = DATESBETWEEN ( Gregorian, DATE ( 2015, 1, 1 ), DATE ( 2015, 12, 31 ) )
RETURN
    UNION (
        ROW ( "Case", "StartDate with time", "Dates", "Date column", "First", MINX ( ColumnWithTime, 'Date'[Date] ), "Last", MAXX ( ColumnWithTime, 'Date'[Date] ), "Days", COUNTROWS ( ColumnWithTime ) ),
        ROW ( "Case", "StartDate with time", "Dates", "Calendar", "First", MINX ( CalendarWithTime, 'Date'[Date] ), "Last", MAXX ( CalendarWithTime, 'Date'[Date] ), "Days", COUNTROWS ( CalendarWithTime ) ),
        ROW ( "Case", "Dates after the data", "Dates", "Date column", "First", MINX ( ColumnAfterData, 'Date'[Date] ), "Last", MAXX ( ColumnAfterData, 'Date'[Date] ), "Days", COUNTROWS ( ColumnAfterData ) ),
        ROW ( "Case", "Dates after the data", "Dates", "Calendar", "First", MINX ( CalendarAfterData, 'Date'[Date] ), "Last", MAXX ( CalendarAfterData, 'Date'[Date] ), "Days", COUNTROWS ( CalendarAfterData ) )
    )

--  Life-to-date sales with a date column and with a calendar.
--  The query filters Sundays: Day of Week is not part of the Gregorian calendar.
--  With the date column of a table marked as a date table, CALCULATE removes
--  all the filters on the Date table, Day of Week included.
--  With the calendar, CALCULATE removes only the filters on the calendar columns.
--  The filter on Day of Week stays: Sales LTD Calendar sums only Sundays.
DEFINE
    MEASURE Sales[Sales LTD Date] =
        CALCULATE (
            [Sales Amount],
            DATESBETWEEN ( 'Date'[Date], BLANK (), MAX ( 'Date'[Date] ) )
        )
    MEASURE Sales[Sales LTD Calendar] =
        CALCULATE (
            [Sales Amount],
            DATESBETWEEN ( Gregorian, BLANK (), MAX ( 'Date'[Date] ) )
        )
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Calendar Year Month],
    'Date'[Calendar Year Month Number],
    TREATAS ( { "CY 2007" }, 'Date'[Calendar Year] ),
    TREATAS ( { "Sunday" }, 'Date'[Day of Week] ),
    "Sales Amount", [Sales Amount],
    "Sales LTD Date", [Sales LTD Date],
    "Sales LTD Calendar", [Sales LTD Calendar]
)
ORDER BY 'Date'[Calendar Year Month Number]