June 15, 2020
Time Functions in DAX Language
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.