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.
Join the conversation! Your thoughts help the community grow.