For Power BI Desktop version 2.76.5678.782 (January 2020).

Time functions in DAX language enable us to filter data to a particular date, period between two dates, Year to Date and similar. Time functions only work if we have a set calendar table. More about calendar tables is covered in articles:

• Calendar in Power BI
• Dynamic Calendar in Power Query
• Dynamic Calendar in Power Query Using an M Function
• Dynamic Calendar in Power BI with DAX function CALENDARAUTO()

After creating a calendar table we must mark it as such. We select Table Tools > Mark as date table. With marked calendar table we can start using time functions.

FIRSTDATE

Returns the first, oldest date in the table.

Syntax

FIRSTDATE(Dates)

Parameter	Description
Dates	Date field of the table.

Example

FIRSTDATE_Example = FIRSTDATE(‘Calendar'[Date])

Returns the first date of the Date field in Calendar table.

LASTDATE

Returns the last, earliest date in the table.

Syntax

LASTDATE(Dates)

Parameter	Description
Dates	Date field of the table.

Example

LASTDATE_Example = LASTDATE(‘Calendar'[Date])

Returns the first date of the Date field in Calendar table.

CLOSINGBALANCEMONTH

Returns value for the last date of the currently selected month.

Syntax

CLOSINGBALANCEOFMONTH(Expression, Dates, [Filter])

Parameter	Description
Expression	Expression, which will be evaluated for the last date of the currently selected month.
Dates	Date field of the Calendar table.

Example

BM_Example = CLOSINGBALANCEMONTH([AC], ‘Calendar'[Date])

Returns the value of AC or Actual measure for the last date of the currently selected month. Calendar[Date] is the location of the calendar table.

CLOSINGBALANCEQUARTER

Returns value for the last date of the currently selected quarter.

Syntax

CLOSINGBALANCEOFQUARTER(Expression, Dates, [Filter])

Parameter	Description
Expression	Expression, which will be evaluated for the last date of the currently selected quarter.
Dates	Date field of the Calendar table.

Example

BM_Example = CLOSINGBALANCEQUARTER([AC], ‘Calendar'[Date])

Returns the value of AC or Actual measure for the last date of the currently selected QUARTER. Calendar[Date] is the location of the calendar table.

CLOSINGBALANCEYEAR

Returns value for the last date of the currently selected year.

Syntax

CLOSINGBALANCEOFYEAR(Expression, Dates, [Filter])

Parameter	Description
Expression	Expression, which will be evaluated for the last date of the currently selected year.
Dates	Date field of the Calendar table.

Example

BM_Example = CLOSINGBALANCEYEAR([AC], ‘Calendar'[Date])

Returns the value of AC or Actual measure for the last date of the currently selected year. Calendar[Date] is the location of the calendar table.

TOTALMTD

Returns value of an expression, from the beginning of the year to the end of the currently selected month.

Syntax

TOTALMTD(Expression, Dates, [Filter], [YearEndDate])

Parameter	Description
Expression	Expression, which will be evaluated from the beginning of the year to the end of currently selected month.
Dates	Date field of the Calendar table.

Example

TOTALMTD_Example = TOTALMTD((SUM(fKnjizbe[Value]),’Calendar'[Date])

Returns sum of Value from the beginning of the year to the end of currently selected month. Calendar[Date] is the location of the calendar table.

TOTALQTD

Returns value of an expression, from the beginning of the year to the end of the currently selected quarter.

Syntax

TOTALQTD(Expression, Dates, [Filter], [YearEndDate])

Parameter	Description
Expression	Expression, which will be evaluated from the beginning of the year to the end of currently selected quarter.
Dates	Date field of the Calendar table.

Example

TOTALQTD_Example = TOTALQTD((SUM(fKnjizbe[Value]),’Calendar'[Date])

Returns sum of Value from the beginning of the year to the end of currently selected quarter. Calendar[Date] is the location of the calendar table.

TOTALYTD

Returns value of an expression, from the beginning to the end of the year.

Syntax

TOTALYTD(Expression, Dates, [Filter], [YearEndDate])

Parameter	Description
Expression	Expression, which will be evaluated from the beginning to the end of the year.
Dates	Date field of the Calendar table.

Example

TOTALQTD_Example = TOTALQTD((SUM(fKnjizbe[Value]),’Calendar'[Date])

Returns sum of Value from the beginning of the year to the end of currently selected quarter. Calendar[Date] is the location of the calendar table.

DATEADD

Returns a table of dates, shifted forward or backward for a given interval.

Syntax

DATEADD(Dates, NumberOfIntervals, Interval)

Parameter	Description
Dates	Date field of the Calendar table.
NumberOfIntervals	Number of intervals to shift the data.
Interval	Interval unit: DAY, MONTH, QUARTER or YEAR.

Example

DATEADD_Example = DATEADD(‘Calendar'[Date], 5, DAY)

Returns a table of currently selected dates, shifted forward for 5 days.

DATESBETWEEN

Returns a table of dates between a given start and end date.

Syntax

DATESBETWEEN(Dates, StartDate, EndDate)

Parameter	Description
Dates	Date field of the Calendar table.
StartDate	Selected start date.
EndDate	Selected end date.

Example

DATESBETWEEN_Example = DATESBETWEEN(‘Calendar'[Date], DATE(2018, 1, 1), DATE(2018, 1, 12))

Returns dates between January 1, 2018 and January 12, 2018.

DATESINPERIOD

Returns a table of dates that start with selected date and run for a given number of intervals.

Syntax

DATESINPERIOD(Dates, StartDate, NumberOfIntervals, Interval)

Parameter	Description
Dates	Date field of the Calendar table.
StartDate	Selected start date.
NumberOfIntervals	Number of intervals.
Interval	Interval unit: DAY, MONTH, QUARTER or YEAR.

Example

DATESINPERIOD_Example = DATESINPERIOD(‘Calendar'[Date], DATE(2018, 1, 1), 10, DAY)

Returns a table of 10 days, starting with January 1, 2018.

PARALELPERIOD

Returns a table of parallel dates to currently selected dates, shifted for a selected interval forward or backward in time.

Syntax

PARALLELPERIOD(Dates, NumberOfIntervals, Interval)

Parameter	Description
Dates	Date field of the Calendar table.
NumberOfIntervals	Interval shift.
Interval	Interval unit: MONTH, QUARTER or YEAR.

Example

PARALLELPERIOD_Example = PARALLELPERIOD(‘Calendar'[Date], 10, MONTH)

Returns a table of dates, parallel to currently selected dates, shifted 10 months forward.

DATESMTD

Returns a table of dates from the start of currently selected month to the currently selected date.

Syntax

DATESMTD(Dates)

Parameter	Description
Dates	Date field of the Calendar table.

Example

DATESMTD_Example = DATESMTD(‘Calendar'[Date])

Returns a table of dates from the start of currently selected month to the currently selected date.

DATESQTD

Returns a table of dates from the start of currently selected quarter to the currently selected date.

Syntax

DATESQTD(Dates)

Parameter	Description
Dates	Date field of the Calendar table.

Example

DATESQTD_Example = DATESQTD(‘Calendar'[Date])

Returns a table of dates from the start of currently selected quarter to the currently selected date.

DATESYTD

Returns a table of dates from the start of currently selected year to the currently selected date.

Syntax

DATESYTD(Dates)

Parameter	Description
Dates	Date field of the Calendar table.

Example

DATESYTD_Example = DATESYTD(‘Calendar'[Date])

Returns a table of dates from the start of currently selected year to the currently selected date.

ENDOFMONTH

Returns the last day of the currently selected month.

Syntax

ENDOFMONTH(Dates)

Parameter	Description
Dates	Date field of the Calendar table.

Example

ENDOFMONTH_Example = ENDOFMONTH(‘Calendar'[Date])

Returns the last day of the currently selected month.

ENDOFQUARTER

Returns the last day of the currently selected quarter.

Syntax

ENDOFQUARTER(Dates)

Parameter	Description
Dates	Date field of the Calendar table.

Example

ENDOFQUARTER_Example = ENDOFQUARTER(‘Calendar'[Date])

Returns the last day of the currently selected quarter.

ENDOFYEAR

Returns the last day of the currently selected year.

Syntax

ENDOFYEAR(Dates)

Parameter	Description
Dates	Date field of the Calendar table.

Example

ENDOFYEAR_Example = ENDOFYEAR(‘Calendar'[Date])

Returns the last day of the currently selected year.