Introduction
In my previous
article, I have shown you the different types of DAX functions. ‘Time Intelligence’ functions are one of them. So, here in this article you will learn about Time Intelligence functions, their syntax, and how to use these functions in the formula.
Time intelligence functions allow you to manipulate data over time periods, including days, months, years and then create and compare calculations over those periods. These functions can be divided in to two categories
- Functions that returns a scalar value without requiring CALCULATE
- Functions that return a table, which has to be used as a filter in a CALCULATEstatement
CLOSINGBALANCEMONTH
Evaluates the specified expression for the date corresponding to the end of the current month after applying specified filter.
Syntax
CLOSINGBALANCEMONTH(Expression, Date, [Filter])
Expression
An expression returns a scalar value.
Date
A column that contains a date.
Filter
An expression that specifies a filter to apply to the current context. It is optional.
Ex.
CLOSINGBALANCEQUARTER
Evaluates the specified expression for the date corresponding to the end of the current Quarter after applying specified filter.
Syntax
CLOSINGBALANCEMONTH (Expression, Date, [Filter])
Expression
An expression returns a scalar value.
Date
A column that contains date.
Filter
An expression that specifies a filter to apply to the current context. It is optional
Ex.
CLOSINGBALANCEYEAR
Evaluates the specified expression for the date corresponding to the end of the current Year after applying specified filter.
Syntax
CLOSINGBALANCEMONTH (Expression, Date, [Filter])
Expression
An expression returns a scalar value.
Date
A column that contains date.
Filter
An expression that specifies a filter to apply to the current context. It is optional.
Ex
DATEADD
Moves the given set of dates by a specified interval.
Syntax
DATEADD(Date, NumberOfIntervals, Intervals)
Date
A date value or column that contains date.
NumberOfIntervals
An integer number of interval to add or subtract from the given date.
Interval
It can be year, month, quarter or day.
Ex.
DATESBETWEEN
Returns a table of single column of date between two given dates. This function is uses as a filter inside the ‘CALCULATE’ function.
Syntax
DATESBETWEEN(Dates, StartDate,EndDate)
Date
A column contains date.
StartDate
A date expression.
EndDate
A date expression
Ex.- Red marked terms shows column of the table.
DATESINPERIOD
Returns the table that contains a single column of dates from the given period. This function uses as a filter in ‘CALCULATE’ function.
Syntax
DATESINPERIOD(Dates, StartDate, NumberOfInterval, Interval)
Dates
A date column.
StartDate
A date expression.
NumberOfInterval
An integer number of interval to add or subtract from the given date.
Interval
Interval can be ‘Year’, ‘month’, ‘quarter’ or ‘day’.
Ex
Red marked term represents the table and its column.
DATESMTD
Returns the table containing single column of the set of dates in the month up to the current date.
Syntax
DATESMTD(Dates)
Dates
A date column.
Ex
DATESQTD
Returns the table containing single column of the set of dates in the quarter up to the current date.
Syntax
DATEQTD(Date)
Date
A date column.
Ex.
DATESYTD
Returns the table containing single column of the set of dates in the year up to current date.
Syntax
DATEQTD(Date)
Date
A date column.
Ex
ENDOFMONTH
Returns the end date of the month.
Syntax
ENDOFMONTH(Date)
Date
A date column
Ex.
ENDOFQUARTER
Returns the end of quarter.
Syntax
ENDOFQUARTER(Date)
Date
A date column.
Ex.
ENDOFYEAR
Return the end of year.
Syntax
ENDOFYEAR(Date)
Date
A date column.
Ex
FIRSTDATE
Returns first non-blank date.
Syntax
FIRSTDATE(Date)
Date
A date column.
Ex
FIRSTNONBLANK
Returns the first value in the column from which the expression has a non-blank value.
Syntax
FIRSTNONBLANK(ColumnName, Expression)
ColumnName
Any column expression
- A reference to any column
- A table with single column.
Expression
An expression evaluated for blanks for each value of Column
Ex.
FIRSTNONBLANKVALUE
Returns the first non-blank value of the expression that evaluates for the column.
Syntax
FIRSTNONBLANKVALUE(ColumnName, Expression)
ColumnName
A reference to any column or a table of single column.
Expression
An expression evaluated for each value of column.
Ex.
Summary
These were some time intelligence functions. I have described them in detail with examples. But Power BI supports more than these functions. I will explain them in my next article. So stay with me. Thanks for reading.