Time Intelligence Functions in Power BI Dax

Introduction –

Time Intelligence Functions - Does the name scare you?

Don’t worry, Microsoft Power BI have some built-in functions that helps to manipulate Time Period related data. You can use these measures for further calculations.

Let us see what Power BI offers and try them out with our Dataset.

Here I am using Superstore Dataset. It has Profit and Sales data for the Orders made and it ranges from Jan 2019 to Dec 2021.

Power BI Offers 35 Time Intelligence Functions. I have categorized them as follows,

Category	Function
Previous	PREVIOUSDAY
	PREVIOUSMONTH
	PREVIOUSQUARTER
	PREVIOUSYEAR
NEXT	NEXTDAY
	NEXTMONTH
	NEXTQUARTER
	NEXTYEAR
START	STARTOFMONTH
	STARTOFQUARTER
	STARTOFYEAR
END	ENDOFMONTH
	ENDOFQUARTER
	ENDOFYEAR
First	FIRSTDATE
	FIRSTNONBLANK
LAST	SAMEPERIODLASTYEAR
	LASTDATE
	LASTNONBLANK
Opening	OPENINGBALANCEMONTH
	OPENINGBALANCEQUARTER
	OPENINGBALANCEYEAR
Closing	CLOSINGBALANCEMONTH
	CLOSINGBALANCEQUARTER
	CLOSINGBALANCEYEAR
Total	TOTALMTD
	TOTALQTD
	TOTALYTD
DATE	DATEADD
	DATESBETWEEN
	DATESINPERIOD
	DATESMTD
	DATESQTD
	DATESYTD
	PARALLELPERIOD

These Functions can be used along with CALCULATE function to get desire results.

Let us see how to use these functions in detail,

Category – PREVIOUS

Functions in this Category provides Previous Period value. This Period can be Day, Month, Quarter or a Year

Code Format –

MEASURE_NAME = CLACULATE ((EXPRESSION, filters_if_any…), PREVIOUSDAY/MONTH/QUARTER/YEAR(DATE_FIELD FROM UR TABLE/ DATE_MEASURES.ATTRIBUTE_YOU WANT TO EXTRACT FROM [DATE/MONTH/QUARTER/YEAR]))

Function - PREVIOUS DAY

 Code Snippet – 
PriviousDayProfit = CALCULATE(sum(Orders[Profit]),
PREVIOUSDAY(Orders[Order Date].[Date]))

Select TABLE visual and drag Order Date and Profit into it.

Click on NEW MEASURE

Rename new Measure as PriviousDayProfit and write a query as shown in below screenshot.

Drag and drop this new Measure into the table

It shows the Previous Day Profit

This function can also help to find Difference between 2 consecutive day’s profit

Function - PREVIOUS Month

PowerBI offers PreviousMonth function which gives all dates from the previous month, based on the first date in the Dates column, in the current context.

Let us try this with our dataset

Here again select a Table Visualization and drag Order DATE and Profit into it. This time level-up to Months.

Then create New Measure and write following formula to get Previous Month profit.

Drag and drop this new measure into the table. And here you go… It gives you previous month’s profit.

Similarly You can work on Previous Quarter and Previous Year functions.

Function - Previous Quarter

Code Snippet - 
PerviousQuarterProfit = CALCULATE(sum(Orders[Profit]),
PREVIOUSQUARTER(Orders[Order Date].[Date]))

Create New Measure as shown below.

Drag and drop this new Measure into the table

Function - Previous Year –

Code Snippet -
 PerviousYearProfit = CALCULATE(SUM(Orders[Profit]),
PREVIOUSYEAR(Orders[Order Date].[Date]))

Create New Measure as shown below.

Drag and drop this new Measure into the table

Category – NEXT

Functions in this Category provides NEXT Period value. This Period can be Day, Month, Quarter or a Year

Code Format –
MEASURE_NAME = CLACULATE ((EXPRESSION, filters_if_any…), NEXTDAY/MONTH/QUARTER/YEAR(DATE_FIELD FROM UR TABLE/ DATE_MEASURES.ATTRIBUTE_YOU WANT TO EXTRACT FROM [DATE/MONTH/QUARTER/YEAR]))

Function - NEXTQUARTER

 Code Snippet – 
NextQuarterProfit = CALCULATE(sum(Orders[Profit]),
NEXTQUARTER(Orders[Order Date].[Date]))

Select TABLE visual and drag Order Date and Profit into it.

Click on NEW MEASURE

Function - NEXTYEAR

 Code Snippet – 
NextYearProfit = CALCULATE(SUM(Orders[Profit]),
NEXTYEAR(Orders[Order Date].[Date]))

Select TABLE visual and drag Order Date and Profit into it.

Level up Order Date upto Year

Click on NEW MEASURE

Write a function as given above. Then Drag this new measure into the table and here you go…it summarize next year’s profit in just one step.

Category – Start

Function – STARTOFMONTH

This function returns first date of the month in the current context for the specified column of dates.

Code Snippet – 
MEASURE_NAME= STARTOFMONTH(DATE_COL_FROM_TABLE)

StartOfMonth = STARTOFMONTH(Orders[Order Date].[Date])

Create New Measure as shown below.

Drag and drop this new Measure into the table

Similarly, you can calculate STARTOFQUARTER & STARTOFYEAR

StartOfQuarter = STARTOFQUARTER(Orders[Order Date].[Date])
StartOfYear = STARTOFYEAR(Orders[Order Date].[Date])

Category – END

Function – ENDOFMONTH

This function returns Last date of the month in the current context for the specified column of dates.

Code Snippet – 
MEASURE_NAME= ENDOFMONTH(DATE_COL_FROM_TABLE)

Create New Measure as shown below.

Drag and drop this new Measure into the table

On the same line one can calculate ENDOFQUARTER and ENDOFYEAR values

These measures can be used for further analysis

Category – DATE

Function – DATEADD

Code Snippet – 
DATEADD ( DATE_COL, NUMBER_OF_INTERVALS,INTERVAL)

Here, if you want to calculate in future compare to today’s the give NUMBER_OF_INTERVALS as a Positive Number.

if you want to calculate in back in time compare to today’s the give NUMBER_OF_INTERVALS as a Negative Number.

Types of Intervals are – Day, Month, Quarter and Year

So one can calculate it as per the need.

Let us see with an example –

Forward In Time -

Create New Measure as shown below.

Drag and drop this new Measure into the table

DATEADD = CALCULATE(SUM(Orders[Profit]), 
DATEADD(Orders[Order Date].[Date],5,DAY))    

Backward in Time –

Create New Measure as shown below.

Drag and drop this new Measure into the table

DATADD_PREV = CALCULATE(SUM(Orders[Profit]),DATEADD(Orders[Order Date].[Date],-5,DAY))

Function - DATESBETWEEN

Returns a table that contains a column of dates that begins with a specified start date and continues until a specified end date.

CODE Snippet -

DATESBETWEEN(<Date>, <StartDate>, <EndDate>)

Here,

Date - is Date Column

Start Date is Date Expression

End Date - Date Expression

Function - DATESINPERIOD

Returns a table that contains a column of dates that begins with a specified start date and continues for the specified number and type of date intervals.

This function is suited to pass as a filter to the CALCULATE function. Use it to filter an expression by standard date intervals such as days, months, quarters, or years

Code Sinppet

DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>

 

Dates - is Date Col

Start_Date is a date expression

Number of Interval - An integer that specifies the number of intervals to add to, or subtract from, the dates.

Interval - The interval by which to shift the dates. The value for interval can be one of the following: DAY, MONTH, QUARTER, and YEAR

Conclusion -

These DAX Time Intelligence Functions helps to manipulate data using time periods, including days, months, quarters, and years, and then build and compare calculations over those periods.

In simple words, these Functions make life easy for difficult Time Related Calculations.