How to create Custom Date Range Slicer using DAX in Power BI

How to create Custom Date Range table using DAX in Power BI
Scenario:

Suppose we have a Data Model as follows:

The Relationships are as follows:

Based on the above Data Model, we can create a Custom Date Range table using the below DAX expression:

DateRange Period =

VAR _Today = TODAY()

VAR _MinDT = MIN(DimCalendar[DimDate])

/*CALCULATE( MIN(DimCalendar[DimDate]), ALLNOBLANKROW(DimCalendar))*/

VAR _MaxDT = MAX(DimCalendar[DimDate])

/*CALCULATE( MAX(DimCalendar[DimDate]), ALLNOBLANKROW(DimCalendar))*/

VAR _WeekDay_of_Today = WEEKDAY(_Today,1)

VAR _WeekStart_Date = _Today-WEEKDAY(_Today,1)+1

VAR _WeekEnd_Date = _Today-WEEKDAY(_Today,1)+7

VAR _MonthStartDt =

    CALCULATE( STARTOFMONTH(DimCalendar[DimDate]),

        FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _MonthEndDt =

    CALCULATE( ENDOFMONTH(DimCalendar[DimDate]),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _PrevMonthStartDt =

    CALCULATE( EOMONTH(STARTOFMONTH(DimCalendar[DimDate]),-2)+1,

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _PrevMonthEndDt =

    CALCULATE( EOMONTH(ENDOFMONTH(DimCalendar[DimDate]),-1),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _CurQtrStartDt =

    CALCULATE( STARTOFQUARTER(DimCalendar[DimDate]),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _CurQtrEndDt =

    CALCULATE( ENDOFQUARTER(DimCalendar[DimDate]),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _PrevQtrStartDt =

    CALCULATE( STARTOFQUARTER(PREVIOUSQUARTER(DimCalendar[DimDate])),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _PrevQtrEndDt =

    CALCULATE( ENDOFQUARTER(PREVIOUSQUARTER(DimCalendar[DimDate])),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _CurYearStartDt =

    CALCULATE( STARTOFYEAR(DimCalendar[DimDate]),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _CurYearEndDt =

    CALCULATE( ENDOFYEAR(DimCalendar[DimDate]),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _PrevYearStartDt =

    CALCULATE( STARTOFYEAR(PREVIOUSYEAR(DimCalendar[DimDate])),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _PrevYearEndDt =

    CALCULATE( ENDOFYEAR(PREVIOUSYEAR(DimCalendar[DimDate])),

       FILTER(ALLNOBLANKROW(DimCalendar),DimCalendar[DimDate] = TODAY()))

VAR _Last7Days_Dates =     DATESBETWEEN( DimCalendar[DimDate],_Today-7, _Today-1)

VAR _Last15Days_Dates =     DATESBETWEEN( DimCalendar[DimDate],_Today-15, _Today-1)

VAR _ThisWeek_Dates =     DATESBETWEEN( DimCalendar[DimDate], _WeekStart_Date, _WeekEnd_Date)

VAR _PrevWeek_Dates =     DATESBETWEEN( DimCalendar[DimDate], _WeekStart_Date-7, _WeekEnd_Date-7)

VAR _MTD_Dates =       DATESBETWEEN( DimCalendar[DimDate], _MonthStartDt, TODAY())

VAR _CurMonth_Dates =     DATESBETWEEN( DimCalendar[DimDate], _MonthStartDt, _MonthEndDt)

VAR _PrevMonth_Dates =     DATESBETWEEN( DimCalendar[DimDate], _PrevMonthStartDt, _PrevMonthEndDt)

VAR _QTD_Dates =     DATESBETWEEN( DimCalendar[DimDate], _CurQtrStartDt, TODAY())

VAR _CurQtr_Dates =     DATESBETWEEN( DimCalendar[DimDate], _CurQtrStartDt, _CurQtrEndDt)

VAR _PrevQtr_Dates =     DATESBETWEEN( DimCalendar[DimDate], _PrevQtrStartDt, _PrevQtrEndDt)

VAR _YTD_Dates =     DATESBETWEEN( DimCalendar[DimDate], _CurYearStartDt, TODAY())

VAR _CurYear_Dates =     DATESBETWEEN( DimCalendar[DimDate], _CurYearStartDt, _CurYearEndDt)

VAR _PrevYear_Dates =     DATESBETWEEN( DimCalendar[DimDate], _PrevYearStartDt, _PrevYearEndDt)

VAR _All_Dates = DATESBETWEEN( DimCalendar[DimDate], _MinDT, _MaxDT)

VAR _Custom_Dates = DATESBETWEEN( DimCalendar[DimDate], BLANK(), BLANK())

VAR _All = ADDCOLUMNS(_All_Dates,

        "Date_Range", "(All)",

        "Range_Index", 1

        )

VAR _Day_Range =

    SELECTCOLUMNS(

            {

            ( _Today, "Today",2),

            ( _Today-1,"Previous Day",3)

            },

        "DimDate",  [Value1],

        "Date_Range", [Value2],

        "Range_Index", [Value3]

            )

VAR _Last7Days =

    ADDCOLUMNS( _Last7Days_Dates, "Date_Range", "Last 7 Days", "Range_Index", 4 )

VAR _Last15Days =

    ADDCOLUMNS( _Last15Days_Dates, "Date_Range", "Last 15 Days", "Range_Index", 5)

VAR _ThisWeek =

    ADDCOLUMNS( _ThisWeek_Dates, "Date_Range", "This Week", "Range_Index", 6)

VAR _PrevWeek =

    ADDCOLUMNS(_PrevWeek_Dates,"Date_Range", "Previous Week", "Range_Index", 7)

VAR _MTD =

    ADDCOLUMNS(_MTD_Dates,"Date_Range", "MTD","Range_Index", 8)

VAR _CurMonth =

    ADDCOLUMNS(_CurMonth_Dates, "Date_Range", "Cur Month", "Range_Index", 9)

VAR _PrevMonth =

    ADDCOLUMNS(_PrevMonth_Dates,"Date_Range", "Prev Month", "Range_Index", 10)

VAR _QTD =

    ADDCOLUMNS(_QTD_Dates, "Date_Range", "QTD","Range_Index", 11)

VAR _CurQTR =

    ADDCOLUMNS(_CurQtr_Dates, "Date_Range", "Cur Quarter", "Range_Index", 12)

VAR _PrevQtr =

    ADDCOLUMNS(_PrevQtr_Dates, "Period_Type", "Prev Quarter", "Range_Index", 13)

VAR _YTD =

    ADDCOLUMNS(_YTD_Dates, "Date_Range", "YTD","Range_Index", 14)

VAR _CurYear =

    ADDCOLUMNS(_CurYear_Dates, "Date_Range", "Cur Year", "Range_Index", 15)

VAR _PreYear =

    ADDCOLUMNS(_PrevYear_Dates, "Date_Range", "Prev Year", "Range_Index", 16)

VAR _Custom =

    ADDCOLUMNS(_Custom_Dates, "Date_Range", "Custom", "Range_Index", 17)

RETURN

UNION(

    _ALL, _Day_Range, _Last7Days,_Last15Days,

    _ThisWeek,_PrevWeek,

    _MTD, _CurMonth, _PrevMonth,

    _QTD, _CurQTR,_PrevQtr,

    _YTD, _CurYear, _PreYear

    )

The result of the above Date Range table is as follows:

Notes:
This table is derived based on the standard Date table (DimCalendar) from the Data Model.

This is a disconnected table which is not required to connect to the Model.

We can use the Range_Index to sort the Date_Range Column.
We have used the ALLNOBLANKROW(DimCalendar) to avoid the Circular dependency error.

-----------------------------------------------------------------------------

Now we will use this custom Date_Range column as a Slicer to filter the data in Report. Lets check how this Slicer works on a Visual.

First lets create a Sales measure needed for the Visual:

Units Sold = SUM(tbl_Sales[Units_Sold])

Next create a Measure which checks whether the Date(from DimCalendar) is within the range of selected custom Date Range. It returns 1 for true and 0 for false.

Is In DateRange =

VAR _MaxDt = MAX(DimCalendar[DimDate])

VAR _IsInDateRange = INT(_MaxDt IN VALUES('DateRange Period'[DimDate]))

RETURN

_IsInDateRange

We will use this Measure as Visual Level filter to filter Dates (from DimCalendar) on X-Axis.

Result:

Visual Level Filter: [Is In DateRange] is 1.

The Date used on X-Axis is from main date table DimCalendar[DimDate] of the Data Model.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------