Sometimes you need to filter page in Power BI to only show YTD values or a custom timeframe that does not exist in the standard date filter types. In these cases, it’s possible to create a custom DAX Measure that determines whether dates are within a specified time frame and then use the measure as a custom filter. We’ll explain the DAX functions and techniques needed.
Table of Contents
The Problem with YTD Filtering in Power BI
The challenge with filtering a Power BI Report Page to YTD is that the built in filter function does not support it. There are options to filter in or before a specific date, which is not dynamic or you can filter on a relative date but only gives you the ability to filter for the current year, not the current YTD.
The screenshot below highlights the issue with YTD filtering in Power BI.
Luckily, there are a few options you can deploy as work arounds.
Filtering YTD in Power BI
To create a YTD filter in Power BI or to filter on a custom date range that’s dynamic, you will need to first create a measure for Today’s date. Then create an IF statement that checks whether the dates in a table are before today’s date. Then add the conditional measure to the filter pane to apply to the report page.
Here’s how in more detail.
Step 1.) Create a Column for Today’s Date
Create a New Column in Power BI to return today’s date. This field will not be used in the report itself but will be used in subsequent calculations to determine if a date in the data table is before or after today’s date. The syntax to create a column for Today’s Date with DAX is:
Today = Today()This creates a new column that we will reference in our next calculation.
Step 2.) Create a Conditional Column in DAX
Create a New Column that uses an IF statement to determine whether the dates in a table are before or after Today. We will return a 1 if a date is before today, and a 0 if a date is after today. Here is the DAX formula for the Measure:
YTD Filter = IF('Date'[Date] < 'Date'[Today],1,0)We are using a table named ‘Date’ with columns named Date and Today to perform our check. The results of the formula are a 1 or 0 based on a date compared to today’s date.
Step 3.) Apply the YTD Filter
Add the new YTD Filter column to the filters pane in Power BI. Switch it to Basic Filtering and select 1. This will filter on all values that are before today’s date.
Using this method ensures that the report is automatically updated to show YTD values based on when the user logs into the report.
Filtering YTD in Power Query vs. DAX
An alternative to using DAX formulas to create new columns for filtering is to use Power Query. The primary difference will be the timing and which the query executes to create the new columns. Power Query will update when a Power BI report is scheduled to refresh, while DAX is dynamic and will update regardless of when the data source was last refreshed.
For large data sources that are updated daily it can make sense to perform calculations in Power Query to determine if a date column is before or after today but in most cases DAX will be easier and quicker to implement.