How to Filter Current Year Data Using Power BI DAX?

Recently, I was working on a Power BI report and needed to filter data for the current year. This is a common requirement for many reports, as it helps in analyzing and visualizing data to the ongoing time period. To achieve this, we can use DAX (Data Analysis Expressions) in Power BI to dynamically filter data based on the current year.

In this Power BI tutorial, I will show you how to filter current year data using Power BI DAX. Along with that, we will see how to filter last year data and calculate Year-to-Date (YTD) sales using Power BI DAX.

Filter Current Year Data Using Power BI DAX

Last week, I was working on a Power BI report that needed to show sales data only for the current year. To achieve this, I used DAX to create a measure that filters the data based on the year. Now, follow the below steps to see how I created it.

For this example, I created a data set that contains SaleID, Date, and Amount columns. You can take your own data set like the one below.

Load the data to the Power BI desktop using the get data option. Then, create a new measure and apply the formula below to find the current year’s sales value.

Sales_CurrentYear = 
CALCULATE(
    SUM(SalesTable[Amount]), 
    YEAR(SalesTable[Date]) = YEAR(TODAY())
)

Where:

• Sales_CurrentYear: This is the name of the measure.
• CALCULATE: This function modifies the context in which the data is evaluated.
• SUM(SalesTable[Amount]): This function sums up the values in the [Amount] column of the ‘SalesTable’.
• YEAR(SalesTable[Date]) = YEAR(TODAY()): This filter condition ensures that only sales from the current year are included. YEAR(TODAY()) gets the current year, and YEAR(SalesTable[Date]) extracts the year from the sales date.

Now, in the Report view, add a Power BI card visual to show the current year’s total sales.

This way, you can see the current year’s value in your Power BI Visual and check whether it is correct by adding another card visual that shows total sales.

Filter Last Year Data using Power BI DAX

Now, we will see how to filter last year’s data using Power BI DAX. We will use the same dataset as above. The table contains data for only two years: 2023 and 2024. In the previous example, we looked at the 2024 sales. Now, let’s see how to get the 2023 sales using Power BI DAX, which was last year.

Create a new measure and apply the formula below to find the last year’s sales value.

Sales_LastYear = 
CALCULATE(
    SUM(SalesTable[Amount]), 
    YEAR(SalesTable[Date]) = YEAR(TODAY()) - 1
)

Where:

• Sales_LastYear: This is the name of the measure.
• CALCULATE: This function changes the context in which the data is evaluated.
• SUM(SalesTable[Amount]): This function sums up the values in the [Amount] column of the ‘SalesTable’.
• YEAR(SalesTable[Date]) = YEAR(TODAY()) – 1: This filter condition ensures that only sales from the previous year are included. YEAR(TODAY()) – 1 gets the previous year, and YEAR(SalesTable[Date]) extracts the year from the sales date.

Now, in the Report view, add a Power BI card visual to show the last year’s total sales.

Filter Year to Date Using Power BI DAX

While working on a Power BI report, I needed to show the total sales amount from the beginning of the current year up to today’s date. To achieve this, I used Power BI DAX TOTALYTD() to create a measure that calculates the Year-to-Date (YTD) sales.

For this example, I am using the same data set, but I added some more rows to it.

Now follow the below steps to calculate Year to Date (YTD) values in Power BI.

I hope you loaded the above data set into Power BI Desktop. Now, create a new table using the DAX expression below.

DateTable = CALENDAR(MIN(SalesTable[Date]), MAX(SalesTable[Date]))

Where:

• DateTable: This is the name of the calculated table.
• CALENDAR: This function creates a table with a single column of dates.
• MIN(SalesTable[Date]): This function finds the earliest date in the [Date] column of the ‘SalesTable’.
• MAX(SalesTable[Date]): This function finds the latest date in the [Date] column of the ‘SalesTable’.

Go to the Model view and create a relationship between two data sets, i.e., the Sales[Date] column and the DateTable[Date] column.

Now go to Report view and create a New measure to show Year-to-Date (YTD) sales using the DAX expression below.

Sales Amount YTD = TOTALYTD(SUM(SalesTable[Amount]),'SalesTable'[Date])

Where:

• Sales Amount YTD: This is the name of the measure.
• TOTALYTD: This function calculates the year-to-date total for a given expression up to the last date in the specified date column, considering the current filter context.
• SUM(SalesTable[Amount]): This function sums up the values in the [Amount] column of the ‘SalesTable’.
• ‘SalesTable'[Date]: This specifies the date column in the ‘SalesTable’ to consider for the year-to-date calculation.

Add a Power BI Table visual to the Report view, then add a date column from the data table and add the Sales Amount and Sales Amount YTD from SalesTable.

This is how you can filter year-to-date using Power BI DAX.

In this Power BI tutorial, we learned how to use Power BI DAX to filter data for the current year and the previous year. Additionally, we covered how to calculate Year-to-Date (YTD) sales using Power BI.

Also, you may like some more Power BI articles:

• How to Filter Date Using Power BI DAX
• Filter Last N Days Data Using Power BI DAX
• Filter Distinct Dates Using Power BI DAX
• How to Filter Between Two Dates in Power BI
• Create Date Hierarchy in Power BI