How to Calculate Overdue Days in Power BI

Calculating how many days an invoice is past due or a task is behind schedule is a fundamental need for almost any business. In Power BI, you can easily track this by using a bit of DAX (Data Analysis Expressions). This guide will walk you through the steps to calculate overdue days, handle different scenarios, and visualize the results in a meaningful way.

Start with Clean Data

Before you write any DAX, your data needs to be in good shape. For calculating overdue days, your table - whether it's for invoices, projects, or support tickets - must have at least two key columns:

• A unique identifier for each item (e.g., InvoiceID, ProjectName, or TicketNumber).
• A due date column (e.g., DueDate or Deadline).

It's critical that your due date column is set to the correct data type. If Power BI sees it as text, the calculations will fail.

To check or change the data type:

1. In Power BI Desktop, click on Transform data to open the Power Query Editor.
2. Find your DueDate column.
3. Select the column header. In the Home tab, look at the Data Type dropdown. Make sure it’s set to Date or Date/Time.
4. Click Close & Apply.

Clean data is the foundation of any reliable report. Taking a moment to verify your formats now will save you from headaches later.

Method 1: Using a DAX Measure (The Flexible Approach)

A measure is a dynamic calculation that runs whenever you interact with your report. It’s perfect for calculating values that depend on the current context, like filters or slicers. Measures are also the most memory-efficient option, as the result isn't stored in your data model.

Our goal is to create a measure that shows the number of days between an item's due date and today, but only if the due date has already passed.

Step 1: Create the Basic "Overdue Days" Calculation

First, let’s build a simple measure that finds the difference between the due date and today.

1. Right-click on your table in the Fields pane and select New measure.
2. When the formula bar appears, enter the following DAX formula:

Overdue Days (Simple) = DATEDIFF(SELECTEDVALUE(Invoices[DueDate]), TODAY(), DAY)

Let’s break that down:

• DATEDIFF: This function calculates the difference between two dates, with three arguments: DATEDIFF(<start_date>, <end_date>, <interval>).
• SELECTEDVALUE(Invoices[DueDate]): Gets the due date for the current item in context (e.g., row in a visual). Using SELECTEDVALUE handles potential multiple values robustly.
• TODAY(): Returns today's date, updates every refresh.
• DAY: Specifies that the difference is in days, other options include MONTH, YEAR, etc.

Step 2: Refine the Measure to Handle Different Statuses

The simple formula works, but it has issues — it can produce negative numbers for items not yet overdue, and it counts overdue days even for completed or paid items. Let’s fix this with an IF condition.

Change the formula to:

_OverdueDays =
IF(
    SELECTEDVALUE(Invoices[DueDate]) < TODAY(),
    DATEDIFF(SELECTEDVALUE(Invoices[DueDate]), TODAY(), DAY),
    0
)

This checks if the DueDate is in the past. If yes, it calculates the overdue days, if not, it returns 0. You could also return BLANK() instead of 0 to exclude non-overdue items from averages.

Now, let’s make it more robust by considering an invoice or task status to avoid marking paid or completed items as overdue.

_OverdueDays =
VAR SelectedDueDate = SELECTEDVALUE(Invoices[DueDate])
VAR CurrentStatus = SELECTEDVALUE(Invoices[Status])
VAR TodayDate = TODAY()

RETURN
IF(
    CurrentStatus <> "Paid" && SelectedDueDate < TodayDate,
    DATEDIFF(SelectedDueDate, TodayDate, DAY),
    BLANK()
)

This measure:

• Stores key values in variables for clarity and efficiency.
• Checks that the status is not "Paid" and the due date is in the past.
• Calculates overdue days only when both conditions hold, otherwise, returns blank.

Method 2: Using a Calculated Column (The Simple Context Approach)

Alternatively, you can create a calculated column, which assigns a value for each row during data load or refresh. Unlike measures, calculated columns are stored in your table, making them suitable for use in slicers or categorization.

When to use a calculated column?

• When you need the value in visualizations without aggregating.
• To categorize items based on overdue days (e.g., "0-30 days overdue").

Creating the Overdue Days Calculated Column

1. In Power BI Desktop, go to the Data view.
2. Select your table.
3. From the Table Tools ribbon, click New Column.
4. Enter:

Overdue Column =
IF(
    'Invoices'[Status] <> "Paid" && 'Invoices'[DueDate] < TODAY(),
    INT(TODAY() - 'Invoices'[DueDate]),
    0
)

Notes:

• 'Invoices'[DueDate] is directly referenced — no SELECTEDVALUE needed, calculated columns operate row-wise.
• TODAY() - 'Invoices'[DueDate] subtracts dates, returning the difference in days.
• INT() rounds down to a whole number.

Visualize Your Overdue Items

Now that you have your calculation, display and analyze it in reports.

Use Conditional Formatting in a Table

1. Create a Table visual.
2. Add columns: InvoiceID, CustomerName, DueDate, and your measure _OverdueDays.
3. In the Visualizations pane, right-click _OverdueDays and choose Conditional formatting > Background color.
4. Select Rules. Define rules such as:

• Value > 0 and <= 30: yellow
• Value > 30 and <= 60: orange
• Value > 60: red

This highlights overdue items based on age.

Create "Overdue Buckets" for High-Level Overview

To group overdue periods, add a calculated column:

Overdue Bucket =
SWITCH(
    TRUE(),
    'Invoices'[Overdue Column] = 0, "Not Overdue",
    'Invoices'[Overdue Column] <= 30, "1-30 Days",
    'Invoices'[Overdue Column] <= 60, "31-60 Days",
    'Invoices'[Overdue Column] <= 90, "61-90 Days",
    "90+ Days"
)

Use this column in visuals like bar or pie charts. For values, count InvoiceID or TaskID. This gives you a quick snapshot of overdue aging.

Final Thoughts

Learning to calculate overdue days unlocks the power of DAX in Power BI. By combining functions like DATEDIFF, TODAY(), and IF, you can turn raw date data into actionable insights. Whether you prefer a dynamic measure for interactive analysis or static categories for reporting, these techniques enhance your ability to monitor overdue items efficiently.

While mastering Power BI and DAX is rewarding, we know sometimes you need quick answers without complex formulas. That’s why we built Graphed—to let you ask questions in plain English and generate live dashboards instantly, pulling from multiple data sources and handling the complex analysis for you.