How to Offset Days and Months in Power BI and Power Query

User Question:

I’m working on an HR dashboard in Power BI, and I need to compare employee performance metrics from the current month with those from 3 months ago. How can I create offset calculations for days and months in Power BI? Is it better to do this in Power Query or DAX?

Offsetting power bi columns by days and months

This tutorial will guide you through implementing date offsets using both Power Query and DAX, allowing you to compare current data with historical periods effectively.

Choose calendar offsets or elapsed-time offsets explicitly

A reliable date-offset logic procedure begins with a controlled test and a clear success condition. Record the current state, account, device or file involved, and the exact symptom before making changes. Apply one material change at a time so the outcome is reversible and useful evidence is preserved.

Run the focused check

Adding one calendar month is not the same as adding a fixed number of days. Define expected behavior for month-end dates, leap years, business days, daylight-saving changes, and timestamps before choosing Power Query or DAX.

Use Power Query when the shifted value is a stable data-preparation attribute. Use DAX when the result must respond to report filter context. Preserve the original date and give the offset column a unit-specific name.

Verify and document the outcome

Test January 31, February 29, year-end, and a timestamp near a daylight-saving transition. Compare results with a small expected-output table. If business days are required, use a proper calendar table with workday indicators rather than a simple duration.

For a managed work environment, check whether the behavior is controlled by policy, licensing, permissions, retention, or a staged feature rollout. A missing or locked control may be intentional. Do not bypass governance locally. Capture the application and operating-system versions, account type, relevant timestamps, and non-sensitive screenshots. This evidence helps support teams identify whether the remedy belongs on the device, in the user profile, in Microsoft 365 administration, or with another service owner.

Repeat the original action after the change and confirm the result from the perspective of another affected user when appropriate. If the test fails, restore the previous state before trying the next option. Keep the final working configuration and the reason for it in the team documentation.

Before closing the case, restart or refresh the affected client and perform the test once more. A result that survives a new session is stronger than a temporary improvement. Note any remaining limitation and the person responsible for follow-up.

Our Data Model

Table Name: EmployeePerformance. Our table contains the following columns:

• EmployeeID (Text)
• Date (Date)
• PerformanceScore (Decimal)

Method 1: Using Power Query

THis method leverages a new custom column and is very beneficial for multiple calculations or when you need the offset date in various contexts.

1. Open Power Query Editor.
2. Select the EmployeePerformance table.
3. Add a custom column:

• Click “Add Column” > “Custom Column”
• Name it “OffsetDate”
• Use the following M formula:

= Date.AddMonths([Date], -3)

4. Hit OK.
5. Close and Apply changes.

Offset by Days in PowerQuery

We can also offset by date in the same fashion. Let’s assume for simplicity that we want to offset by 90 days:

= Date.AddDays([Date], -90)

Method 2: Using DAX

Using DAX is more flexible for dynamic calculations, especially when you need different time offsets in different measures.

1. Create a new measure in Power BI Desktop using the following DAX code:

Current Month Performance = 
AVERAGEX(
    FILTER(EmployeePerformance, 
        EmployeePerformance[Date] = MAX(EmployeePerformance[Date])
    ),
    EmployeePerformance[PerformanceScore]
)

2. Create another measure for the offset comparison using the following DAX code:

Three Months Ago Performance = 
AVERAGEX(
    FILTER(EmployeePerformance, 
        EmployeePerformance[Date] = EDATE(MAX(EmployeePerformance[Date]), -3)
    ),
    EmployeePerformance[PerformanceScore]
)

3. Last, go ahead and create a performance difference measure:

Performance Difference = 
[Current Month Performance] - [Three Months Ago Performance]

Troubleshooting

1. If date formats are incorrect – Ensure dates are in a consistent format recognized by Power BI.
2. If you get blank results – Check if your date range includes data for both current and offset periods.
3. Unexpected results near month/year boundaries: Be aware of how EDATE handles month-end dates, especially for months with different numbers of days.