How to Get Current Quarter in Power BI

Calculating the current quarter is a fundamental task for building timely and relevant dashboards in Power BI. Instead of manually updating your filters every three months, you can use a simple DAX formula to do the work for you, ensuring your reports always default to a view of the present. This article walks you through creating a dynamic "current quarter" calculation from scratch.

Why Is a Dynamic "Current Quarter" Filter Important?

Most business reporting revolves around specific timeframes: daily, weekly, monthly, and quarterly. Quarterly goals are a cornerstone of business strategy, from sales targets to marketing budgets. When you hard-code a filter to "Q3 2024," that report immediately becomes outdated once October 1st rolls around. Someone has to remember to go in and manually change the filter to "Q4 2024."

By creating a dynamic calculation that automatically identifies the current quarter, you solve this problem permanently. Your reports become more intelligent and require less maintenance. When your CEO opens the sales dashboard, they won’t see last quarter's numbers by mistake. Instead, they will instantly see up-to-date performance, enabling quicker and more informed decisions.

A few common use cases include:

• Sales Dashboards: Displaying a team's sales-to-date against their quarterly target.
• Marketing Reports: Tracking quarterly ad spend, conversion rates, and ROI without manual date adjustments.
• Financial Summaries: Creating KPI cards that show "Current Quarter Revenue" or "Current Quarter Profit."

The Foundation: You Need a Proper Date Table

Before you can perform any meaningful time intelligence calculations in Power BI, you absolutely need a dedicated calendar or date table. Relying on default date hierarchies that Power BI creates automatically can lead to inconsistent results and limitations down the road.

A proper date table is a simple table with one column containing a continuous list of dates, from your earliest transaction to a future date. It cannot have any skipped dates and should have a one-to-many relationship with the date columns in your fact tables (e.g., your sales or web traffic data).

How to Create a Basic Date Table with DAX

If you don't have a date table from your data warehouse, you can create one in seconds using DAX.

1. In Power BI Desktop, navigate to the Data view (the grid icon on the left).
2. In the Home tab of the ribbon, click New Table.
3. Enter the following DAX formula. This will create a table with a single column called "Date" that automatically finds the first and last dates in your entire data model and fills in all dates between them.

Date = CALENDARAUTO()

Once you've created this base table, you should add helper columns to it that will make your filtering and analysis easier. Click on your newly created 'Date' table, go to the Table tools tab, and click New Column for each of the following formulas:

• Year: Year = YEAR('Date'[Date])
• Quarter Number: QuarterNumber = QUARTER('Date'[Date])
• Quarter Name: (Adds a "Q" prefix for readability) Quarter = "Q" & FORMAT('Date'[Date], "Q")
• Year & Quarter: (Combines the year and quarter for sorting) YearQuarter = FORMAT('Date'[Date], "YYYY") & " Q" & FORMAT('Date'[Date], "Q")
• Month Number: MonthNumber = MONTH('Date'[Date])
• Month Name: MonthName = FORMAT('Date'[Date], "mmmm")

After creating these columns, go to the Model view and drag the 'Date'[Date] column onto the date column in your primary data table (like 'Sales'[OrderDate]) to create the relationship. Now you're ready to build the current quarter logic.

Method 1: Creating an "Is Current Quarter" Flag as a Calculated Column

The most straightforward way to filter by the current quarter is to create a calculated column in your date table that acts as a flag. This column will contain a simple, human-readable value like "Current Quarter" for dates that fall within the present quarter and another value like "Other Quarters" for all other dates.

Step-by-Step Instructions

Step 1: Navigate to Your Date Table

In the Data view in Power BI, select the Date table you just created.

Step 2: Create a New Column

From the main ribbon, click New Column.

Step 3: Enter the DAX Formula

In the formula bar, type or paste the following DAX expression:

IsCurrentQuarter = VAR TodayYear = YEAR(TODAY()) VAR TodayQuarter = QUARTER(TODAY()) VAR CurrentRowYear = YEAR('Date'[Date]) VAR CurrentRowQuarter = QUARTER('Date'[Date]) RETURN IF( TodayYear = CurrentRowYear && TodayQuarter = CurrentRowQuarter, "Current Quarter", "Past Quarter" )

Breaking Down the Formula

Let's look at what each part of this formula does. Using variables (VAR) makes the formula much easier to read and maintain.

• VAR TodayYear = YEAR(TODAY()): Creates a variable holding the current year.
• VAR TodayQuarter = QUARTER(TODAY()): Creates a variable holding the current quarter number.
• VAR CurrentRowYear = YEAR('Date'[Date]): Gets the year of the current row's date.
• VAR CurrentRowQuarter = QUARTER('Date'[Date]): Gets the quarter of the current row's date.
• IF(...): Checks if both the year and quarter match the current date's year and quarter. If yes, returns "Current Quarter", otherwise, "Past Quarter."

Once you add this column, your Date table will have a new indicator for whether a date is in the current quarter. You can now easily filter your visuals to show only the current quarter or compare current versus past quarters.

Method 2: Using a DAX Measure for Filtering

An alternative to a calculated column is a DAX measure. The key difference is that a calculated column is computed once during data refresh and stored in your data model, consuming memory. A measure is calculated on the fly only when you use it in a visual.

Measures are great for keeping your model clean and efficient, especially with large date tables. You can use a measure in the filter pane of a visual or across the whole report.

Step-by-Step Instructions

1. Create a New Measure

Right-click on your Date table in the Fields pane and select New measure.

2. Enter the DAX Formula for the Measure

Filter IsCurrentQuarter = IF( MAX('Date'[YearQuarter]) = FORMAT(TODAY(), "YYYY") & " Q" & FORMAT(TODAY(), "Q"), 1, 0 )

3. Using the Measure to Filter a Visual

Now, select a visual on your report canvas, such as a bar chart showing sales by month. Then:

• Drag your Filter IsCurrentQuarter measure onto the Filters on this visual section in the Filters pane.
• Expand the filter settings.
• Under "Show items when the value," select "is" from the dropdown.
• Enter 1 in the text box.
• Click Apply filter.

Your visual will instantly update to show only data for the current quarter. You can apply this same filter at the page or report level for consistency across all visuals.

Putting It All to Work: Practical Examples

Now that you have your IsCurrentQuarter column or measure, here are a few ways to use it.

1. Create a "Current Quarter Sales" Card

You can create a measure that calculates total sales only for the current quarter. This is ideal for KPI cards.

Current Quarter Sales = CALCULATE( [Total Sales], 'Date'[IsCurrentQuarter] = "Current Quarter" )

The CALCULATE function modifies the filter context to include only the rows where the IsCurrentQuarter column is "Current Quarter."

2. Add a Slicer for Easy Navigation

Drag the IsCurrentQuarter column onto your report and convert it into a slicer. Users can toggle between "Current Quarter" and "Past Quarter" with a simple click.

3. Set a Default View

Drag the IsCurrentQuarter column into the Filters on this page section and select "Current Quarter." When users open the report page, it will default to showing only the current quarter's data but allow them to change filters or slicers to view other periods.

Final Thoughts

By creating a dynamic current quarter flag in your Date table, you make your Power BI reports smarter, more automated, and less prone to user error. Whether you choose a calculated column for simple filtering or a DAX measure for more efficient models, this simple technique elevates your reports from static snapshots to timely, dynamic dashboards that focus your audience on what matters now.

Constantly staying on top of real-time performance is what separates effective teams from the rest. While mastering DAX is a powerful skill, sometimes the overhead of managing measures and calculated columns can add up. At Graphed, we've automated this entire process. Rather than building flags and filter contexts, you can simply connect your data sources and ask questions in plain English, like "show me our total sales for an interactive LOB for the current quarter," and get a live, interactive dashboard built for you in seconds.