How to Calculate Current Month Sales in Power BI

Tracking your current month's sales in Power BI is essential for understanding your business's real-time performance. Instead of waiting for a month-end report, you can gain immediate insights into how you're pacing against your goals. This article will walk you through a step-by-step process using DAX to create a measure that automatically calculates and displays your sales for the current month.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

First Things First: Prerequisites

Before jumping into the formulas, you need a couple of things ready in your Power BI file to ensure everything works smoothly. This setup is a best practice that will make all your time-based calculations easier, not just this one.

• Power BI Desktop: This tutorial assumes you have the free Power BI Desktop application installed on your computer.
• A Sales Data Table: You need a table containing your sales information. At a minimum, this table should have two columns: one with the sale amount (e.g., 'Revenue' or 'Sales Amount') and another with the transaction date (e.g., 'OrderDate').
• A Calendar Table: For robust time-based analysis in Power BI, a dedicated Calendar Table is non-negotiable. While you can sometimes get away with using the default date hierarchies, a separate Calendar Table provides the flexibility and power needed for accurate calculations. We'll build one together in the next step.

Why You Absolutely Need a Calendar Table

You might be tempted to skip creating a Calendar Table, especially if your sales data already has a date column. Don't! A dedicated Calendar Table, also known as a Date Table, acts as the single source of truth for all things date-related in your data model.

Here’s why it's so important:

• @Consistency: It ensures that you have a continuous range of dates, without any gaps. Your sales data might not have a sale every single day, which can cause issues with certain time intelligence functions. A calendar table fills in those missing days.
• @Enhanced Analysis: It allows you to add useful columns for slicing and dicing your data, such as Year, Quarter, Month Name, Day of the Week, and financial year markers.
• @Time Intelligence Power: Most of Power BI's powerful time intelligence DAX functions (like TOTALMTD, SAMEPERIODLASTYEAR, etc.) are designed to work with a proper Calendar Table.

Setting it up takes a few minutes but will save you hours of headaches down the road.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Creating a Dynamic Calendar Table with DAX

Let's create a Calendar Table using a simple DAX expression. This table will automatically update based on the range of dates in your sales data.

1. From the Report or Data view in Power BI, navigate to the Modeling tab in the ribbon.
2. Click on New Table.
3. In the formula bar that appears, enter the following DAX formula. Be sure to replace 'YourSalesTableName'[YourDateColumn] with the actual names of your sales table and date column.

Date Table = 
ADDCOLUMNS (
    CALENDAR ( MIN ( 'YourSalesTableName'[YourDateColumn] ), MAX ( 'YourSalesTableName'[YourDateColumn] ) ),
    "Year", YEAR ( [Date] ),
    "MonthNumber", MONTH ( [Date] ),
    "MonthName", FORMAT ( [Date], "mmmm" ),
    "DayOfWeek", WEEKDAY ( [Date], 2),
    "DayName", FORMAT([Date], "dddd")
)

Breaking Down the Formula

• @CALENDAR(): This function generates a table with a single column named "Date" containing a continuous set of dates. We set the start and end of this range by finding the earliest (MIN) and latest (MAX) dates from your sales table's date column. This makes it dynamic, as new sales data is loaded, the calendar will automatically extend.
• @ADDCOLUMNS(): We wrap the CALENDAR() function with ADDCOLUMNS() to add more useful columns to our new table for easier reporting.
• @"Year", "MonthNumber", etc.: These are the new columns we're creating. Each uses a simple function like YEAR(), MONTH(), or FORMAT() to extract a specific part of the date from the base [Date] column.

After creating the table, select it, go to the Table tools tab, and click Mark as date table. In the dialog box, select the 'Date' column you just created. This tells Power BI that this is your official calendar for time intelligence calculations.

Connecting Your Tables: Creating the Relationship

Now that you have your 'Sales Data' table and your new 'Date Table', you need to tell Power BI how they relate to each other.

1. Go to the Model view (the icon with three connected boxes on the far left).
2. You will see boxes representing your tables. Find the date column in your 'Date Table' (e.g., 'Date').
3. Find the corresponding date column in your Sales Data table (e.g., 'OrderDate').
4. Click and drag the 'Date' column from your 'Date Table' and drop it directly onto the 'OrderDate' column in your Sales table.

You should see a line appear between the two tables, with a "1" on the 'Date Table' side and an asterisk (*) on the sales table side. This signifies a one-to-many relationship, which is exactly what we want.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Writing the DAX Measure for Current Month Sales

With our data model properly set up, it's time to write the DAX measure that will perform our calculation. A measure is a formula for a calculation that you can use in any of your visuals.

1. Go back to the Report view.
2. Select your Sales table from the Fields pane on the right.
3. Click New Measure from the Table tools or ribbon menu.
4. In the formula bar, enter the following DAX formula:

Current Month Sales = 
CALCULATE(
    SUM('YourSalesTableName'[Sales Amount]),
    FILTER(
        ALL('Date Table'),
        'Date Table'[MonthNumber] = MONTH(TODAY()) &&
        'Date Table'[Year] = YEAR(TODAY())
    )
)

Remember to replace 'YourSalesTableName'[Sales Amount] with your actual sales column and 'Date Table' with the name of your calendar table.

What's Happening in This Formula?

This formula might look complex, but it's doing a few very logical things. Let's break it down:

• TODAY(): This simple function returns the current date. So if it's the 15th of June, 2024, it returns that date. YEAR(TODAY()) would return 2024, and MONTH(TODAY()) would return 6.
• ALL('Date Table'): This function is critical. It removes any existing filters that might be applied to the Date Table. This ensures our calculation only looks at the current month, regardless of what the user has selected elsewhere.
• FILTER(...): This function iterates over the entire, unfiltered 'Date Table' and keeps only the rows that meet our criteria:
• SUM('YourSalesTableName'[Sales Amount]): This is the simple part of our calculation. We're telling Power BI to sum up the values in the 'Sales Amount' column.
• CALCULATE(...): This is the most powerful function in DAX. It modifies the context of a calculation. Here, it’s taking our base calculation (the SUM of sales) and applying the new context we created with our FILTER function. In plain English, it says, "Sum the sales, but only for the dates that belong to the current month and year."

A Simpler Way: Using the TOTALMTD Function

Now that you understand the manual way to build the logic, there's an even cleaner and often preferred method using Power BI's built-in time intelligence functions. The TOTALMTD function is designed for exactly this purpose.

Create another new measure and use this formula:

Current Month Sales (MTD) = 
TOTALMTD(
    SUM('YourSalesTableName'[Sales Amount]),
    'Date Table'[Date]
)

That's it! This compact formula achieves the same result. The TOTALMTD (Total Month-to-Date) function takes an expression (our SUM of sales) and a date column, and it automatically calculates the sum for the month-to-date period based on the latest date in the context. Since we don't have a filter, it uses today's date.

This approach is simpler, easier to read, and less prone to errors, which is why it's recommended once you have a correctly configured and marked Date Table.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Visualizing Your Result

You've created the measure, now let's put it on your report canvas. The best visual depends on what you want to show.

• Card Visual: Perfect for a prominent, single-number KPI. Just drag your new 'Current Month Sales' measure onto the canvas and select the Card visual from the Visualizations pane.
• Gauge Visual: Use a Gauge if you want to show your current month's sales relative to a monthly target. Drag your measure into the 'Value' field and your sales target into the 'Target value' field.
• Bar or Column Chart: Compare your 'Current Month Sales' to your 'Previous Month Sales' (a similar DAX measure) or a goal. This helps you track performance over time.

Final Thoughts

Calculating your current month's sales in Power BI becomes straightforward once you implement the foundational best practice of using a dedicated Calendar Table. Whether you manually construct the logic with FILTER or use the more direct TOTALMTD function, DAX provides the tools you need to create responsive, real-time metrics for your business dashboard.

Creating step-by-step calculations and connecting different data sources can take time. If you simply want to see your key business metrics without writing DAX or building reports manually, we've designed Graphed to do the heavy lifting for you. We connect to your data sources like Google Analytics, Shopify, and Salesforce, and let you create real-time dashboards just by describing what you want to see - like "show me our total sales for this month." It's like having a data analyst on your team who works in seconds.