Time Intelligence DAX Functions In Power BI - Part One

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
  1. Functions that returns a scalar value without requiring CALCULATE
  2. 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.
 
Time Intelligence DAX Functions In Power BI
 

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.
 
Time Intelligence DAX Functions In Power BI
 

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
 
Time Intelligence DAX Functions In Power BI
 

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.
 
Time Intelligence DAX Functions In Power BI
 

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.
 
Time Intelligence DAX Functions In Power BI
 

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.
 
Time Intelligence DAX Functions In Power BI
 

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
 
Time Intelligence DAX Functions In Power BI
 

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.
 
Time Intelligence DAX Functions In Power BI
 

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
 
Time Intelligence DAX Functions In Power BI
 

ENDOFMONTH

 
Returns the end date of the month.
 
Syntax
 
ENDOFMONTH(Date)
 
Date
 
A date column
 
Ex.
 
Time Intelligence DAX Functions In Power BI
 

ENDOFQUARTER

 
Returns the end of quarter.
 
Syntax
 
ENDOFQUARTER(Date)
 
Date
 
A date column.
 
Ex.
 
Time Intelligence DAX Functions In Power BI
 

ENDOFYEAR

 
Return the end of year.
 
Syntax
 
ENDOFYEAR(Date)
 
Date
 
A date column.
 
Ex
 
Time Intelligence DAX Functions In Power BI
 

FIRSTDATE

 
Returns first non-blank date.
 
Syntax
 
FIRSTDATE(Date)
 
Date
 
A date column.
 
Ex
 
Time Intelligence DAX Functions In Power BI
 

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
  1. A reference to any column
  2. A table with single column.
Expression
 
An expression evaluated for blanks for each value of Column
 
Ex.
 
Time Intelligence DAX Functions In Power BI
 

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.
 
Time Intelligence DAX Functions In Power BI
 

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.