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

by

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.

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)
  1. Hit OK.
  2. 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]
)
  1. 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]
)
  1. 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.