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

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

How to Identify which Products are having Sales in selected Date Range using DAX

How to Identity Max Sales Date and Order value per Product using Power BI DAX
Scenario:

Suppose we have a Data Model as follows:

The Relationships are as follows:

Based on the above Data Model, we want to find the which Products are having the sales in the selected Date Range.
If the Product is not having any Sales in that selected period, then we should show its status as In-Active.

Also, we want to find what is the Last Order date and Last Order value per Product in selected Date Range, and in the overall Date Range of Orders.

We can achieve this Scenario using the below DAX expressions:

Units Sold in Selected Date Range = SUM(tbl_Sales[Units_Sold])

Last Order Date per Prod in Selected Date Range = MAX(tbl_OrderDetails[Order_Date])

Last Order Value per Prod in Selected Date Range =

VAR _SelectProd = SELECTEDVALUE(tbl_Products[Prod_Id])

VAR _LastOrder =

    MAXX( FILTER(ALLSELECTED(tbl_OrderDetails), tbl_OrderDetails[Prod_Id]=_SelectProd),

        tbl_OrderDetails[Order_Date]

    )

VAR _LastOrder_Value =

    CALCULATE( SUM(tbl_Sales[Units_Sold]),

        FILTER(ALL(tbl_OrderDetails),tbl_OrderDetails[Order_Date]=_LastOrder))

RETURN

_LastOrder_Value

Is Product InActive in Selected DateRange =

VAR _AllProd =  FILTER(VALUES(tbl_Products[Prod_Id]),

                                                tbl_Products[Prod_Id]<>BLANK()

                                            )

VAR _SoldProd = FILTER(VALUES(tbl_OrderDetails[Prod_Id]) ,

                                                tbl_OrderDetails[Prod_Id]<>BLANK()

                                            )

VAR _ExceptVal = COUNTROWS(EXCEPT(_AllProd, _SoldProd))

RETURN

IF ( AND(ISBLANK(_ExceptVal), [Units Sold in Selected Date Range]),0,_ExceptVal)

Product Sale Status =

SWITCH( TRUE(),

    [Is Product InActive in Selected DateRange] = 1,"In Active",

     [Units Sold in Selected Date Range]<> BLANK(), "Active"

    )

The following measures returns the Last Order_Id, Order_Date and Order Value across Total Orders and all the Order dates per Product.

Last Order Date per Product =

VAR _SelectProd = SELECTEDVALUE(tbl_Products[Prod_Id])

VAR _LastOrder =

    MAXX( FILTER(ALL(tbl_OrderDetails), tbl_OrderDetails[Prod_Id]=_SelectProd),

        tbl_OrderDetails[Order_Date]

    )

RETURN

_LastOrder

Last Order_Id per Product =

VAR _SelectProd = SELECTEDVALUE(tbl_Products[Prod_Id])

VAR _LastOrder =

    MAXX( FILTER(ALL(tbl_OrderDetails), tbl_OrderDetails[Prod_Id]=_SelectProd),

        tbl_OrderDetails[Order_Date]

    )

VAR _LastOrder_Value =

    CALCULATE( LASTNONBLANK(tbl_OrderDetails[Order_Id],TRUE()),

        FILTER(ALL(tbl_OrderDetails),tbl_OrderDetails[Order_Date]=_LastOrder))

RETURN

_LastOrder_Value

Last Order Value per Product =

VAR _SelectProd = SELECTEDVALUE(tbl_Products[Prod_Id])

VAR _LastOrder =

    MAXX( FILTER(ALL(tbl_OrderDetails), tbl_OrderDetails[Prod_Id]=_SelectProd),

        tbl_OrderDetails[Order_Date]

    )

VAR _LastOrder_Value =

    CALCULATE( SUM(tbl_Sales[Units_Sold]),

        FILTER(ALL(tbl_OrderDetails),tbl_OrderDetails[Order_Date]=_LastOrder))

RETURN

_LastOrder_Value

Results:

Notes: In the Calendar table, we can use the following Column expression to set the Flag as 1 if the Calendar Date is falls within the Range of Min and Max of Sale date.

IsSaleDate =

VAR _MaxSaleDt = MAX(tbl_OrderDetails[Order_Date])

VAR _MinSaleDt = (EOMONTH(MIN(tbl_OrderDetails[Order_Date]),-1) +1 )

VAR _SaleDtFlag = INT( DimCalendar[DimDate]>=_MinSaleDt &&                                         DimCalendar[DimDate] <= _MaxSaleDt)

RETURN

_SaleDtFlag

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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

How to calculate Sales for exact same period last Year using DAX in Power BI

How to calculate Sales for exact same period in previous Year using DAX in Power BI
Scenario:

Suppose we have a Data Model as follows:

The relationships between tables are as follows:

The sample data for the Current Year (2023) and Previous Year (2022) is as per below:

In the below sample data, for Current Year (2023), the data is only available for until 03rd May of W1.

For the Previous Year (2022) we have data for the Whole Year, until the last Week of Year.

In this Case, if we want to calculate the Sales for same Period Last Year, we need to consider only the same dates ( From 01Jan2022 to 03May2022).

Units Sold = SUM(tbl_Sales[Units_Sold])

Now we will see how we can calculate the Sales (Units Sold) for the same period last using various Methods.

1) Using SAMEPERIODLASTYEAR Method:

The following is a very standard method that considers all the Dates in Previous Year instead of exact dates. Hence this method will NOT return the accurate results.

Units Sold LY =

CALCULATE( [Units Sold],

        SAMEPERIODLASTYEAR(DimCalendar[DimDate]))

        )

YoY Growth =

VAR _CY = [Units Sold]

VAR _LY = [Units Sold LY]

VAR _Ratio = DIVIDE(_CY,_LY)-1

RETURN

IF ( _CY<>BLANK() && _LY<>BLANK(), _Ratio, BLANK())

2) Using DATESBETWEEN Method:

The following method considers all Dates in Previous Year until end of the same Month (May) based on the Max Sales Month in Current Year.

This method also will NOT return the accurate results, as the Current Month (May) is not yet completed in the Current Year.

Units Sold LY (till Same Month) =

VAR _MinDtLY = EDATE(MIN(DimCalendar[DimDate]),-12)

VAR _LimitMaxDtLY = EOMONTH(MAX(tbl_OrderDetails[Order_Date]),-12)

VAR _MaxSaleDT = MAX(tbl_OrderDetails[Order_Date])

VAR _LY_Dates = DATESBETWEEN(DimCalendar[DimDate], _MinDtLY, _LimitMaxDtLY)

VAR _LY_Sales = CALCULATE( [Units Sold], _LY_Dates)

VAR _Result = IF(_MinDtLY<=_MaxSaleDT, _LY_Sales, BLANK())

RETURN

_Result

YoY Growth (till Same Month) =

VAR _CY = [Units Sold]

VAR _LY = [Units Sold LY (till Same Month)]

VAR _Ratio = DIVIDE(_CY,_LY)-1

RETURN

IF ( _CY<>BLANK() && _LY<>BLANK(), _Ratio, BLANK())

The results of the above 2 Methods are as follows:

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

3) Using SAMEPERIODLASTYEAR Method (same Dates from LY):
This method considers only exact same dates from the Last Year to calculate Sales for same period Last Year.

Units Sold LY (till Same Dates Method1) =

VAR _MinDtLY = EDATE(MIN(DimCalendar[DimDate]),-12)

/* VAR _LimitMaxDtLY = EOMONTH(MAX(tbl_OrderDetails[Order_Date]),-12) */

VAR _MaxSaleDT = MAX(tbl_OrderDetails[Order_Date])

VAR _LimitDatesLY =

    SAMEPERIODLASTYEAR(

        FILTER(VALUES(DimCalendar[DimDate]),

            DimCalendar[DimDate]<= _MaxSaleDT

            )

        )

VAR _LY_Sales = CALCULATE( [Units Sold], _LimitDatesLY)

VAR _Result = IF(_MinDtLY<=_MaxSaleDT, _LY_Sales, BLANK())

RETURN

_Result

YoY Growth (Same Dates Method1) =

VAR _CY = [Units Sold]

VAR _LY = [Units Sold LY (till Same Dates Method1)]

VAR _Ratio = DIVIDE(_CY,_LY)-1

RETURN

IF ( _CY<>BLANK() && _LY<>BLANK(), _Ratio, BLANK())

4) Using DATESBETWEEN Method (same Dates from LY):
This method considers only exact same dates from the Last Year to calculate Sales for same period Last Year.

Units Sold LY (till Same Dates Method2)=

VAR _MinDtLY = EDATE(MIN(DimCalendar[DimDate]),-12)

/* VAR _LimitMaxDtLY = EOMONTH(MAX(tbl_OrderDetails[Order_Date]),-12) */

VAR _MaxSaleDT = MAX(tbl_OrderDetails[Order_Date])

VAR _LimitMaxDtLY =

    LASTDATE(

        SAMEPERIODLASTYEAR(

            FILTER(VALUES(DimCalendar[DimDate]),

                DimCalendar[DimDate]<= _MaxSaleDT

                )

            )

        )

VAR _LY_Dates = DATESBETWEEN(DimCalendar[DimDate], _MinDtLY, _LimitMaxDtLY)

VAR _LY_Sales = CALCULATE( [Units Sold], _LY_Dates)

VAR _Result = IF(_MinDtLY<=_MaxSaleDT, _LY_Sales, BLANK())

RETURN

_Result

YoY Growth (Same Dates Method2) =

VAR _CY = [Units Sold]

VAR _LY = [Units Sold LY (till Same Dates Method2)]

VAR _Ratio = DIVIDE(_CY,_LY)-1

RETURN

IF ( _CY<>BLANK() && _LY<>BLANK(), _Ratio, BLANK())

The results of the above 2 Methods are as follows:

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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