▼

Saturday, April 15, 2023

How the DATEADD Function Works with Dates in Power BI DAX

How the DAX Function DATEADD works with Dates in Power BI
The DateAdd is a DAX Time Intelligence function of Power BI that adds or subtracts a number of days/months/quarters/years from or to a date field. This Function can work on an interval of Day, Month, Quarter, or Year.
Syntax:
DateAdd(<date field>, <number of intervals>, <interval>)

Example:
Suppose we have a Data Model as follows.


In the above Model, we have a Date table "tbl_Calendar" with the dates between 01/01/2010 to 31/12/2020 :
MinDate = MIN(tbl_Calendar[cDate])
MaxDate = MAX('ParallelPeriod'[cDate])

-------------------------------------------------------------------------------------------------
Now lets discuss about how the DATEADD function works in various Scenarios.

Scenario 1 :  DATEADD with -1 Year interval:
In the following case, the function returns all the available Dates from the first available Month and Year of the tbl_Calendar to till last available Date of 1 Year before the last Year(2020) of the Calendar table.

DateAdd =
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],-1, YEAR)
    )
------------- OR ---------------
DateAdd =
VAR vStartDate = DATE(2015, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],-1, YEAR),
    OR(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:

Case1: (with a Specified Date):
In the following case, it returns a date value from Last Year based on the vBaseDate.

DateAdd =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],-1, YEAR),
    tbl_Calendar[cDate] = vBaseDate
    )

Result:

Case2: (with AND(>=StartDate, <=EndDate)):
In the below case it returns the all available dates from Previous Year based on the vStartDate till the Previous Year based on vEndDate.

DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],-1, YEAR),
    AND(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:
-------------------------------------------------------------------------------------------------
Scenario 2 :  DATEADD with 0 Year interval:
In the following case, it returns all the available dates from first available Month and Year of the Calendar to last available Date in the Month and Year of the Calendar. 

DateAdd =
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],0, YEAR)
    )
------------- OR ---------------
DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],0, YEAR),
    OR(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:

Case1: (with a Specified Date):
In the following case, it returns a same date value from Current Year based on the vBaseDate.

DateAdd =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],0, YEAR),
    tbl_Calendar[cDate] = vBaseDate
    )

Result:

Case2: (with AND(>=StartDate, <=EndDate)):
In the below case it returns the all available dates from vStartDate till vEndDate.

DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],0, YEAR),
    AND(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:
-------------------------------------------------------------------------------------------------
Scenario 3 :  DATEADD with +1 Year interval:
In the following case, it returns all the available dates from Next Year of first available Month and Year of the Calendar to last available Date in the Month and Year of the Calendar. 
DateAdd =
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, YEAR)
    )
------------- OR ---------------
DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, YEAR),
    OR(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:

Case1: (with a Specified Date):
In the following case, it returns a same date value from Next Year based on the vBaseDate.

DateAdd =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, YEAR),
    tbl_Calendar[cDate] = vBaseDate
    )

Result:

Case2: (with AND(>=StartDate, <=EndDate)):
In the below case it returns the all available dates from Next Year based on the vStartDate till the Next Year based on vEndDate.

DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, YEAR),
    AND(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:
-------------------------------------------------------------------------------------------------
Scenario 4 :  DATEADD with -1 Quarter interval:
In the following case, the function returns all the available Dates from the first available Month and Year of the tbl_Calendar to till one Quarter prior to the last Quarter of the last Year(2020) of the Calendar table.

DateAdd =
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],-1, Quarter)
    )
------------- OR ---------------
DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],-1, Quarter),
    OR(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:

Case1: (with a Specified Date):
In below case, it returns a Quarter(3 Months ago) back date value based on the vBaseDate.

DateAdd =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, Quarter),
    tbl_Calendar[cDate] = vBaseDate
    )

Result:

Case2: (with AND(>=StartDate, <=EndDate)):
In the below case it returns all available dates from a Previous Quarter based on vStartDate till the Previous Quarter based on vEndDate.

DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, Quarter),
    AND(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:
-------------------------------------------------------------------------------------------------
Scenario 5 :  DATEADD with 0 Quarter interval:
In the following case, it returns all the available dates from first available Quarter and Year of the Calendar to last available Date in the Quarter and Year of the Calendar. 

DateAdd =
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],0, Quarter)
    )
------------- OR ---------------
DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],0, Quarter),
    OR(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:


Case1: (with a Specified Date):
In below case, it returns a same Date value from Current Quarter based on vBaseDate.

DateAdd =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],0, Quarter),
    tbl_Calendar[cDate] = vBaseDate
    )

Result:

Case2: (with AND(>=StartDate, <=EndDate)):
In the below case it returns all available dates from vStartDate till  vEndDate.

DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],0, Quarter),
    AND(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:
-------------------------------------------------------------------------------------------------
Scenario 6 :  DATEADD with +1 Quarter interval:
In the following case, it returns all the available dates from Next Quarter of the first available Quarter and Year of the Calendar to last available Date in the Quarter and Year of Calendar. 

DateAdd =
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, Quarter)
    )
------------- OR ---------------
DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, Quarter),
    OR(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:

Case1: (with a Specified Date):
In below case, it returns a Date value from Next Quarter based on vBaseDate.

DateAdd =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, Quarter),
    tbl_Calendar[cDate] = vBaseDate
    )

Result:


Case2: (with AND(>=StartDate, <=EndDate)):
In the below case it returns the all available dates from Next Quarter based on the vStartDate till the Next Quarter based on vEndDate.

DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, Quarter),
    AND(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:
-------------------------------------------------------------------------------------------------
Scenario 7 :  DATEADD with -1 Month interval:
In the following case, the function returns all the available Dates from the first available Month and Year of the tbl_Calendar to till one Month prior to the last Month of the last Year(2020) of the Calendar table.

DateAdd =
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],-1, Month)
    )
------------- OR ---------------
DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],-1, Month),
    OR(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:

Case1: (with a Specified Date):
In below case, it returns a Month back date value based on the vBaseDate.

DateAdd =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],-1, Month),
    tbl_Calendar[cDate] = vBaseDate
    )

Result:


Case2: (with AND(>=StartDate, <=EndDate)):
In the below case it returns all available dates from a Previous Month based on vStartDate till the Previous Month based on vEndDate.

DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],-1, Month),
    AND(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:
-------------------------------------------------------------------------------------------------
Scenario 8 :  DATEADD with 0 Month interval:
In the following case, it returns all the available dates from first available Month and Year of the Calendar to last available Date in the Month and Year of the Calendar. 

DateAdd =
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],0, Month)
    )
------------- OR ---------------
DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],0, Month),
    OR(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:

Case1: (with a Specified Date):
In below case, it returns a same Date value from Current Month based on vBaseDate.

DateAdd =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],0, Month),
    tbl_Calendar[cDate] = vBaseDate
    )

Result:

Case2: (with AND(>=StartDate, <=EndDate)):
In the below case it returns all available dates from vStartDate till  vEndDate.

DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],0, Month),
    AND(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:
-------------------------------------------------------------------------------------------------
Scenario 9 :  DATEADD with +1 Month interval:
In the following case, it returns all the available dates from Next Month of the first available Month and Year of the Calendar to last available Date in the Month and Year of Calendar. 

DateAdd =
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, Month)
    )
------------- OR ---------------
DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, Month),
    OR(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:

Case1: (with a Specified Date):
In below case, it returns a Date value from Next Month based on vBaseDate.

DateAdd =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, Month),
    tbl_Calendar[cDate] = vBaseDate
    )

Result:

Case2: (with AND(>=StartDate, <=EndDate)):
In the below case it returns the all available dates from Next Month based on the vStartDate till the Next Month based on vEndDate.

DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, Month),
    AND(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:
-------------------------------------------------------------------------------------------------
Scenario 10 :  DATEADD with -1 Day interval:
In the following case, the function returns all the available Dates from the first available Month and Year of the tbl_Calendar to till one Day prior to the last Date of last Month and Year(2020) of the Calendar table.

DateAdd =
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],-1, Day)
    )
------------- OR ---------------
DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],-1, Day),
    OR(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:

Case1: (with a Specified Date):
In below case, it returns a Day back date value based on the vBaseDate.

DateAdd =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],-1, Day),
    tbl_Calendar[cDate] = vBaseDate
    )

Result:

Case2: (with AND(>=StartDate, <=EndDate)):
In the below case it returns all available dates from a Previous Day based on vStartDate till the Previous Day based on vEndDate.

DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],-1, Month),
    AND(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:
-------------------------------------------------------------------------------------------------
Scenario 11 :  DATEADD with 0 Day interval:
In the following case, it returns all the available dates from first available Month and Year of the Calendar to last available Date in the Month and Year of the Calendar. 

DateAdd =
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],0, Day)
    )
------------- OR ---------------
DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],0, Day),
    OR(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:

Case1: (with a Specified Date):
In below case, it returns a same Date value from Current Date based on vBaseDate.

DateAdd =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],0, Day),
    tbl_Calendar[cDate] = vBaseDate
    )

Result:

Case2: (with AND(>=StartDate, <=EndDate)):
In the below case it returns all available dates from vStartDate till  vEndDate.

DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],0, Month),
    AND(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:
-------------------------------------------------------------------------------------------------
Scenario 12 :  DATEADD with +1 Day interval:
In the following case, it returns all the available dates from Next Day of the first available Month and Year of the Calendar to last available Date in the Month and Year of Calendar. 

DateAdd =
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, Day)
    )
------------- OR ---------------
DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, Day),
    OR(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:

Case1: (with a Specified Date):
In below case, it returns a Date value from Next Day based on vBaseDate.

DateAdd =
VAR vBaseDate = DATE(2015, 07, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, Day),
    tbl_Calendar[cDate] = vBaseDate
    )

Result:

Case2: (with AND(>=StartDate, <=EndDate)):
In the below case it returns the all available dates from Next Day based on the vStartDate till the Next Day based on vEndDate.

DateAdd =
VAR vStartDate = DATE(2014, 07, 15)
VAR vEndDate = DATE(2015, 05, 15)
RETURN
CALCULATETABLE(
    DATEADD(tbl_Calendar[cDate],1, Month),
    AND(
    tbl_Calendar[cDate] >= vStartDate,
    tbl_Calendar[cDate] <= vEndDate
    )
    )

Result:

Notes: DATEADD vs PARALLELPERIOD:
The DATEADD works on the period dynamically (like SamePeriodLastYear), but the ParallelPeriod works statically on the interval mentioned as the parameter.The DateAdd Function can work on an interval of Day, Month, Quarter, or Year. The ParallelPeriod function only works on intervales of Month, Quarter, and Year.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
at

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.