Time Intelligence with DAX Functions: PREVIOUSDAY(), PREVIOUSMONTH(), PREVIOUSQUARTER(), and PREVIOUSYEAR()

3 min readJun 13, 2023

Introduction

In the world of data analysis and business intelligence, understanding and analyzing data across different time periods is essential. The Microsoft Power BI and Excel’s Data Analysis Expressions (DAX) language provides a powerful set of functions to perform time intelligence calculations. In this article, we will explore four important DAX functions — PREVIOUSDAY(), PREVIOUSMONTH(), PREVIOUSQUARTER(), and PREVIOUSYEAR(). These functions allow us to retrieve data from the previous day, month, quarter, and year, respectively. We will delve into their syntax, usage, and provide examples with sample data to illustrate their practical applications.

PREVIOUSDAY() Function

The PREVIOUSDAY() function retrieves the value of a specified measure or column for the previous day in a context. It is useful for calculating day-over-day changes or analyzing trends. The syntax for the PREVIOUSDAY() function is:

PREVIOUSDAY([<dates>])

Example 1

Calculating Daily Sales Variation Suppose we have a sales table with columns “Date” and “SalesAmount.” To calculate the difference in sales amounts between two consecutive days, we can use the PREVIOUSDAY() function:

Daily Variation = SalesTable[SalesAmount] - 
PREVIOUSDAY(SalesTable[SalesAmount])

PREVIOUSMONTH() Function

The PREVIOUSMONTH() function allows us to retrieve data from the previous month. We widely use it for analyzing monthly trends or comparing data between different months. The syntax for the PREVIOUSMONTH() function is:

PREVIOUSMONTH([<dates>])

Example 2

Monthly Sales Comparison Consider a sales table with columns “Date” and “SalesAmount.” To compare the sales amounts between the current month and the previous month, we can use the PREVIOUSMONTH() function:

Monthly Comparison = SUM(SalesTable[SalesAmount]) - 
CALCULATE(SUM(SalesTable[SalesAmount]), PREVIOUSMONTH(SalesTable[Date]))

PREVIOUSQUARTER() Function

The PREVIOUSQUARTER() function retrieves data from the previous quarter. It is helpful for analyzing quarterly performance or identifying patterns across quarters. The syntax for the PREVIOUSQUARTER() function is:

PREVIOUSQUARTER([<dates>])

Example 3

Quarterly Revenue Analysis Suppose we have a revenue table with columns “Date” and “Revenue.” To analyze the change in revenue between the current quarter and the previous quarter, we can use the PREVIOUSQUARTER() function:

Quarterly Analysis = SUM(RevenueTable[Revenue]) - 
CALCULATE(SUM(RevenueTable[Revenue]), PREVIOUSQUARTER(RevenueTable[Date]))

PREVIOUSYEAR() Function

The PREVIOUSYEAR() function retrieves data from the previous year. I commonly use it for year-over-year comparisons or identifying yearly trends. The syntax for the PREVIOUSYEAR() function is:

PREVIOUSYEAR([<dates>])

Example 4

Yearly Sales Growth Consider a sales table with columns “Date” and “SalesAmount.” To calculate the growth rate in sales between the current year and the previous year, we can employ the PREVIOUSYEAR() function:

Yearly Growth = (SUM(SalesTable[SalesAmount]) -
 CALCULATE(SUM(SalesTable[SalesAmount]), PREVIOUSYEAR(SalesTable[Date]))) / 
CALCULATE(SUM(SalesTable[SalesAmount]), PREVIOUSYEAR(SalesTable[Date]))

Conclusion

Time intelligence calculations are crucial for analyzing and understanding data trends over different time periods. The PREVIOUSDAY(), PREVIOUSMONTH(), PREVIOUSQUARTER(), and PREVIOUSYEAR() functions in DAX provide a powerful set of tools to retrieve data from the previous day, month, quarter, and year, respectively.

In this article, we explored the syntax and usage of these functions and provided practical examples with sample data. We saw how the PREVIOUSDAY() function can calculate day-to-day variations in sales amounts. The PREVIOUSMONTH() function proved useful for comparing sales amounts between the current month and the previous month.

We also showed how the PREVIOUSQUARTER() function enables us to analyze quarterly performance by comparing data between the current and previous quarters. Finally, we used the PREVIOUSYEAR() function to calculate year-over-year sales growth.

By leveraging these functions, analysts and business intelligence professionals can gain valuable insights into data trends and make informed decisions. Time intelligence calculations are essential for spotting patterns, identifying anomalies, and understanding the impact of time on various business metrics.

As you delve deeper into the world of DAX programming and time intelligence, remember to explore other related functions and concepts such as SAMEPERIODLASTYEAR(), TOTALMTD(), and YTD(). These functions, in combination with the ones discussed in this article, will equip you with a comprehensive toolkit to tackle a wide range of time-based data analysis tasks.

In conclusion, the PREVIOUSDAY(), PREVIOUSMONTH(), PREVIOUSQUARTER(), and PREVIOUSYEAR() functions in DAX provide valuable capabilities for performing time intelligence calculations. Understanding how to use these functions effectively can enhance your ability to analyze and interpret data across different time periods, enabling you to make data-driven decisions and uncover meaningful insights in your analysis.