How to Calculate Current Month in Power BI

Constantly updating your Power BI reports to show only the current month's data can feel like a chore. This article breaks down exactly how to create dynamic filters and calculations in Power BI that automatically track the current month, saving you from manual updates forever.

Why Does Filtering for the Current Month Matter?

In business, timing is everything. Stakeholders, from sales managers to marketing leads, want to see what's happening right now. Analyzing current month data helps you answer critical, time-sensitive questions:

• Are we on track to hit this month's sales targets?
• How are our new marketing campaigns performing this month?
• Is inventory keeping up with this month's demand?
• How is website traffic trending over the past few weeks?

By building reports that automatically focus on the current month, you create a "single source of truth" that provides real-time visibility into performance. This empowers teams to make faster, more informed decisions without waiting for someone to manually refresh a spreadsheet at the end of every week - a much-needed upgrade to your business performance tracker.

The Foundation: You Need a Proper Date Table

Before we touch any time-based calculations in DAX (Data Analysis Expressions), it's a best practice to have a dedicated date table. A date table is a simple table that contains a continuous list of dates and related columns like year, quarter, month, and day of the week.

Why is this so important?

• It's an established standard. Nearly all time intelligence functions in Power BI and other analytics tools are designed to work with a dedicated date table.
• You avoid errors. You're using the one "official" version of time within your model instead of multiple (often incomplete) date columns from your data sources. So you can see every business day's performance at a glance.
• It adds context. When you create your date table right, it automatically contains columns like Month Name, Day of Week, and Fiscal year. Otherwise, you’re trying to generate these one report at a time, which takes up time and introduces the potential for human error.

How to Create a Simple Date Table Using DAX

If you don't have one, you can easily create one inside Power BI. On the Data tab, select New Table from the ribbon and paste in the following DAX code. Make sure to adjust the start and end dates (2020 and 2025) to match your company's data.

DateTable = 
ADDCOLUMNS (
    CALENDAR (DATE(2020, 1, 1), DATE(2025, 12, 31)),
    "Year", YEAR ( [Date] ),
    "Quarter", "Q" & FORMAT ( [Date], "q" ),
    "MonthNumber", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "mmmm" ),
    "YearMonth", FORMAT ( [Date], "YYYY-MM" )
)

Once created, go to the Model view and connect your new DateTable to your main data table (like a sales or marketing table) by dragging the 'Date' column from the DateTable onto the corresponding date column in your other table. With this foundation in place, let's get to filtering.

Method 1: Create a "Current Month" Flag with a Calculated Column

One of the most straightforward methods is to add a new column to your Date Table that acts as a simple flag, telling us if any given date falls within the current month. It's easy to create and even easier to use in slicers and filters.

The logic is simple: we compare the year and month of each date in our table to the year and month of today's date.

Step-by-Step Instructions

1. Navigate to the Data view in Power BI and select your DateTable.
2. From the top ribbon, click on New Column.
3. In the formula bar, paste the following DAX expression:

IsCurrentMonth = 
VAR CurrentYear = YEAR(TODAY())
VAR CurrentMonth = MONTH(TODAY())
VAR RowYear = 'DateTable'[Year]
VAR RowMonth = 'DateTable'[MonthNumber]

RETURN
IF (
    RowYear = CurrentYear && RowMonth = CurrentMonth,
    "Current Month",
    "Other"
)

Breaking Down the Formula

• VAR CurrentYear = YEAR(TODAY()): This creates a variable called CurrentYear and stores the year from today's date (returned by the TODAY() function).
• VAR CurrentMonth = MONTH(TODAY()): Similarly, this variable stores the current month number.
• VAR RowYear = 'DateTable'[Year] and VAR RowMonth = 'DateTable'[MonthNumber]: These variables store the year and month for each specific row in the DateTable as the formula calculates row-by-row.
• RETURN IF(...): This checks the logic. If the row's year and month match the current year and month, it returns the text "Current Month", otherwise, it returns "Other".

Using Your New Column in a Report

Now that you have this IsCurrentMonth column, you can use it to filter any visual or an entire report page. Simply drag the IsCurrentMonth column into the Filters on this page section of the Filters pane, select "Current Month," and you're done! Every visual on the page will now be filtered to show only data from this month, and it will update automatically on the first day of every new month.

Method 2: Dynamically Calculate Current Month Sales with a Measure

Calculated columns are great for simple filtering, but for more advanced calculations, measures are the way to go. A measure is a formula that is calculated on the fly as you interact with your report. It doesn’t store any data itself, making your data model leaner and often faster.

We'll create a measure that specifically calculates a value (like total sales) but only for the dates in the current month.

Step-by-Step Instructions

1. Make sure you're in the Report view.
2. Go to the Home tab and click on New Measure.
3. In the formula bar that appears, enter the DAX formula below. You'll need to replace SUM('YourFactTable'[Revenue]) with the column you are actually trying to sum (e.g., 'Sales'[OrderTotal], 'Shopify'[net_sales]).

Current Month Revenue =
CALCULATE (
    SUM ( 'YourFactTable'[Revenue] ),
    FILTER (
        ALL ( 'DateTable' ),
        'DateTable'[Year] = YEAR ( TODAY () )
            && 'DateTable'[MonthNumber] = MONTH ( TODAY () )
    )
)

Breaking Down the Formula

• CALCULATE(...): This is one of the most powerful functions in DAX. It modifies the context in which a calculation is performed. Put simply, it’s letting you run a calculation with a filter on.
• SUM('YourFactTable'[Revenue]): This is the sales dashboard calculation we want to perform – the "what."
• FILTER(...): This second part is our filter context – the "where."
• ALL('DateTable'): This temporarily removes any other existing filters on the DateTable (like from a slicer showing all months) so our "current month" condition can be applied cleanly.
• 'DateTable'[Year] = YEAR(TODAY()) && 'DateTable'[MonthNumber] = MONTH(TODAY()): This is the same logic as our calculated column. It tells the FILTER function to only include rows where the date's year and month match today's year and month.

You can now add this Current Month Revenue measure to a Card visual or a chart to display your sales for just this month. Because it always references TODAY(), it will update automatically.

Calculated Column vs. Measure: Which One Should You Use?

Both methods achieve the same goal, so which is better? The answer depends on your goal.

Use a Calculated Column When:

• You want to use a slicer and give your report users an easy toggle like “Current Month” vs “Other”
• You simply need to filter a report and you or your colleagues are beginners in DAX because the logic is easy to understand
• As long as your table isn’t too large (millions of rows), the performance impact will be really minimal

Use a Measure When:

• You need greater flexibility in your reports
• You want higher - even fractionally higher - performance
• It should be the default way you calculate your financial KPIs

For most day-to-day scenarios, a calculated column is a great place to start due to its simplicity. As your reports get more complex, learning to rely on measures will improve your model's performance and flexibility.

Bonus Tip: Calculating Month-to-Date (MTD)

Once you are filtering for the current month, the next logical question is often "what are our sales month-to-date?" Thankfully, DAX has a built-in time intelligence function that makes this incredibly easy.

Create a New Measure with the following formula:

Revenue MTD = 
TOTALMTD (
    SUM ( 'YourFactTable'[Revenue] ),
    'DateTable'[Date]
)

This single, simple measure will calculate a running total of your revenue value for the current period based on any month you're filtering by. When used with our "Current Month" filter, it effectively shows the Month-to-Date performance for this month. If you put it in a line chart that breaks out by week or date, it will keep trending towards your sales quota for the month.

Final Thoughts

Whether you choose the simplicity of a calculated column or the dynamic power of a measure, you now have the tools to create Power BI reports that are always focused on the current month's performance. Using DAX functions like TODAY(), YEAR(), and MONTH() with a proper date table will save you hours of manual updates and keep your team dialed in on the metrics that matter most right now.

Learning the nuances of an advanced business intelligence tool like Power BI can be tough and takes time. That’s why we built Graphed to bypass the complexity of report development entirely. Instead of writing formulas, you connect your data sources (like Google Analytics, Shopify, and your CRM) one-time and then simply ask for what you need in plain English - like "Show me a dashboard of my sales for the current month compared to last month." We provide you with real-time dashboards and deeper insights, freeing up your team to focus on making better business decisions rather than developing yet another business intelligence report.