Contact us

Send
inqury

Knowledge base

home banner

bi@unija.com

  • Knowledge

  • Categories

June 15, 2020

Time Functions in DAX Language

Author: Branka Trifunović

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:

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.

 

Index