How the DATEADD Function Works with Dates in Power BI DAX | Power BI Blog

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)):

Source link

How the DATEADD Function Works with Dates in Power BI DAX | Power BI Blog

Be the first to comment

Leave a Reply Cancel reply