How to Calculate MTD in Power BI

Need to track your progress toward this month's goals in real time? Adding a Month-to-Date (MTD) calculation to your Power BI reports is one of the most practical ways to monitor performance and make timely decisions. This guide will walk you through the different ways to create MTD calculations using DAX, from the simple, everyday functions to more robust formulas.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why You Need MTD Calculations

Month-to-Date analysis shows you the cumulative value of a metric from the beginning of the current month right up to today’s date. For example, if it's the 15th of the month, your MTD sales total shows the sum of all sales from the 1st through the 15th.

This is incredibly useful for a few reasons:

• Goal Tracking: Are you on pace to hit your monthly sales quota, marketing lead goal, or website traffic target? MTD figures tell you exactly where you stand.
• Early Trend Spotting: If your MTD revenue is significantly lower by the second week of the month than it normally is, you know you have an issue to investigate. It allows you to be proactive rather than waiting for an end-of-month surprise.
• Contextual Performance: Comparing this month's MTD against last month's MTD for the same day provides a powerful, apples-to-apples performance comparison.

Adding these dynamic calculations transforms your dashboard from a static historical report into a live performance monitoring tool.

Before You Begin: The Importance of a Date Table

Before you write any DAX time intelligence formulas, you need a proper Date Table. This is the single most important best practice for working with dates in a Power BI model. Power BI’s time intelligence functions (like the ones we’re about to use) rely on an unbroken, continuous sequence of dates to work correctly.

Your sales table or marketing data likely has gaps - days where no sales occurred, like weekends or holidays. A dedicated Date Table solves this by providing one row for every single day in a given period, creating the foundation for your calculations.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Create a Simple Date Table

If you don’t already have one, you can create a Date Table in seconds using DAX.

1. Go to the Data view in Power BI.
2. Select the Table tools tab and click New table.
3. Enter the following DAX formula. This will create a table named 'Date' with a single column of dates from January 1, 2022, to December 31, 2025. Adjust the dates to fit your data's range.

Date = CALENDAR(DATE(2022, 1, 1), DATE(2025, 12, 31))

After creating it, you must tell Power BI that this is your official date table:

• Select your newly created 'Date' table.
• Go to the Table tools tab.
• Click on Mark as date table and select the 'Date' column. This ensures Power BI uses it correctly for time analysis.

Creating the Relationship

Finally, go to the Model view and connect your Date Table to your main data table (e.g., your sales or marketing data). Drag the Date column from your Date Table and drop it onto the corresponding date column in your data table (like OrderDate or PurchaseDate). This creates the one-to-many relationship needed for the formulas to work.

With your Date Table set up, you're ready to start building calculations.

Step 1: Create a Base Measure

It's always best to build your DAX calculations on top of a base measure rather than directly referencing a column. This makes your formulas more modular and easier to read.

Let's say your sales data is in a table called 'Sales' with a column for 'Revenue'.

1. Right-click on your 'Sales' table in the Fields pane and select New measure.
2. Create a basic measure to sum your total revenue:

Total Revenue = SUM(Sales[Revenue])

This simple measure will be the building block for our MTD calculation.

Method 1: The Quick and Easy Way with DATESMTD

The DATESMTD function is one of the most common DAX functions for time intelligence. It returns a table containing a column of all the dates in the current month, up to the last date in the filtered context. We'll use it inside the powerful CALCULATE function.

CALCULATE modifies the context in which a calculation is performed. Here, we'll tell it to calculate our Total Revenue measure, but only for the dates specified by DATESMTD.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

The DAX Formula

Create a new measure and enter the following formula:

Revenue MTD = 
CALCULATE(
    [Total Revenue],
    DATESMTD('Date'[Date])
)

Breaking it Down:

• Revenue MTD =: This is the name of our new measure.
• CALCULATE([Total Revenue], ...): This tells Power BI to take our [Total Revenue] measure and apply a new filter to it.
• DATESMTD('Date'[Date]): This is the filter itself. It provides a table of dates from the beginning of the current month up to the last visible date. By placing this inside CALCULATE, we're telling DAX to sum the revenue only for dates that fall within this MTD period.

And that’s it! You can now drag this Revenue MTD measure into a card visual or a table to see your month-to-date performance. As the context changes (e.g., you use a filter or slicer for a specific month), the measure will update automatically.

Method 2: The All-in-One Powerhouse - TOTALMTD

Power BI often provides multiple DAX functions to achieve the same goal, and MTD is a perfect example. The TOTALMTD function offers a slightly more streamlined syntax because it’s specifically designed for this purpose.

In technical terms, TOTALMTD is "syntactic sugar" for CALCULATE(..., DATESMTD(...)). It essentially wraps the logic from Method 1 into a single function, making your DAX a bit cleaner and easier for others to read.

The DAX Formula

Create another new measure with this formula:

Revenue MTD (TOTALMTD) = 
TOTALMTD(
    [Total Revenue],
    'Date'[Date]
)

Breaking it Down:

• TOTALMTD(...): This function takes care of the context change on its own.
• [Total Revenue]: The first argument is the expression or measure we want to evaluate.
• 'Date'[Date]: The second argument is the column of dates from our official Date Table.

You’ll notice this achieves the exact same result as Method 1, but with slightly less typing. For a simple MTD calculation, many users prefer the readability of TOTALMTD. However, the CALCULATE approach in Method 1 is more versatile if you ever need to add more complex filtering logic to your measure later on.

Bonus Round: Calculating QTD and YTD

Now that you’ve mastered MTD, calculating Quarter-to-Date (QTD) and Year-to-Date (YTD) is incredibly simple. The pattern is exactly the same - you just swap out the function name.

Following the CALCULATE pattern from Method 1:

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Quarter-to-Date (QTD)

Revenue QTD = 
CALCULATE(
    [Total Revenue],
    DATESQTD('Date'[Date])
)

Year-to-Date (YTD)

Revenue YTD =  
CALCULATE(
    [Total Revenue],
    DATESYTD('Date'[Date])
)

By learning one pattern (CALCULATE + Time Intelligence Function), you've unlocked MTD, QTD, and YTD analysis.

Common MTD Traps and How to Fix Them

Running into blank visuals or error messages when creating these measures is common. Here are the most frequent culprits:

1. No Date Table: Trying to run DATESMTD or TOTALMTD on your OrderDate column directly from your sales table is the most common mistake. These functions require an unbroken sequence of dates, which only a dedicated Date Table can guarantee.
2. Missing Relationship: If your Date Table isn't connected to your data table in the Model view, Power BI doesn't know how to filter your data using your dates. Always check for that solid line connecting the two tables.
3. Incorrectly Marked Date Table: Forgetting to "Mark as date table" can sometimes lead to unpredictable behavior. Make sure Power BI officially recognizes your Date Table.

If your MTD calculation isn’t working, nine times out of ten, the problem lies with the setup of your Date Table. Revisit those first steps, and you’ll likely discover the fix.

Final Thoughts

Calculating MTD in Power BI is a fundamental skill that elevates your reporting from simple summaries to dynamic performance dashboards. By setting up a proper Date Table and using DAX functions like CALCULATE with DATESMTD or the more direct TOTALMTD, you can create powerful, real-time insights that help teams track progress and stay on target.

While mastering DAX in Power BI is a valuable skill, it often spotlights the time teams spend manually building and maintaining reports. We created Graphed because we believe getting business answers shouldn't require learning a new query language or navigating a complex interface. By connecting your data sources and asking questions in plain English, you can instantly generate real-time dashboards and reports — allowing your team to focus on what the numbers mean instead of how to calculate them.