PREVIOUSYEAR

How to Use the Power BI DAX function PREVIOUSYEAR

What is the PREVIOUSYEAR function?

PREVIOUSYEAR is a DAX function in Power BI that returns a table that contains a copy of the input table, with the values from the previous year. This function is used to calculate values for a given year, based on the values of the previous year.

The syntax of the PREVIOUSYEAR function is as follows:


PREVIOUSYEAR(❰table❱)


Where `❰table❱` is the input table that contains the date column.

How to use the PREVIOUSYEAR function

Using the PREVIOUSYEAR function in Power BI is relatively straightforward. Here are the steps you need to follow:

1. Open Power BI Desktop and create a new report.

2. Import the data that you want to use in your report.

3. Create a new measure by clicking on the “New measure” button on the ribbon. In the formula bar, enter the following formula:


Sales Previous Year = CALCULATE(SUM(Sales[Sales Amount]), PREVIOUSYEAR('Date'[Date]))


In this formula, we are creating a new measure called “Sales Previous Year” that calculates the sum of the “Sales Amount” column from the “Sales” table for the previous year. The PREVIOUSYEAR function is used to specify the previous year based on the “Date” column.

4. Once you have created the measure, you can use it in your report by dragging and dropping it onto the canvas. You can also apply filters to the report to further analyze the data.

Examples of using the PREVIOUSYEAR function

Let’s take a look at some examples of using the PREVIOUSYEAR function in Power BI.

Example 1: Calculating sales growth

Suppose you have a table that contains the sales data for your business. You want to calculate the sales growth rate for each month compared to the same month in the previous year. Here’s how you can do it using the PREVIOUSYEAR function:

1. Create a new measure by clicking on the “New measure” button on the ribbon. In the formula bar, enter the following formula:


Sales Growth = DIVIDE(SUM(Sales[Sales Amount]), SUM(PREVIOUSYEAR(Sales[Sales Amount])))-1


In this formula, we are creating a new measure called “Sales Growth” that calculates the sales growth rate by dividing the sum of the “Sales Amount” column from the “Sales” table by the sum of the “Sales Amount” column from the previous year. The result is then subtracted by 1 to get the sales growth rate.

2. Once you have created the measure, you can use it in your report by dragging and dropping it onto the canvas. You can also apply filters to the report to further analyze the data.

Example 2: Analyzing trends over time

Suppose you have a table that contains the monthly sales data for your business for the past five years. You want to analyze the trend in sales over time. Here’s how you can do it using the PREVIOUSYEAR function:

1. Create a new measure by clicking on the “New measure” button on the ribbon. In the formula bar, enter the following formula:


Sales Trend = (SUM(Sales[Sales Amount]) - SUM(PREVIOUSYEAR(Sales[Sales Amount])))/SUM(PREVIOUSYEAR(Sales[Sales Amount]))


In this formula, we are creating a new measure called “Sales Trend” that calculates the percentage change in sales from the previous year. The formula subtracts the sum of the “Sales Amount” column from the current year by the sum of the “Sales Amount” column from the previous year and then divides it by the sum of the “Sales Amount” column from the previous year.

2. Once you have created the measure, you can use it in your report by dragging and dropping it onto the canvas. You can also apply filters to the report to further analyze the data.

The PREVIOUSYEAR function in Power BI is a powerful tool that allows you to analyze data effectively and gain insights into trends and patterns. By following the steps outlined in this article, you can use the PREVIOUSYEAR function to calculate values from the previous year and create insightful reports.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

11-13 November 2024

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)