Calculation Issues

Power BI DAX Count Rows Based on the Minimum Date

Huda Qurayshi Nov 12, 2026 869 views

Question details

The user needs DAX measures that identify the earliest case date, count records or unique serial numbers associated with that date, and avoid calculating Weeks Behind when no date is available.

Power BI DAX Count Rows Based on the Minimum Date

Product: Microsoft Power BI or Power Pivot
Device & OS: not provided
Scenario: A data model contains case dates and serial numbers, and the user wants queue counts and elapsed weeks based on the oldest case date.
Observed behavior: The model must count rows or distinct serial numbers for the minimum date and return a blank Weeks Behind result when the minimum date is blank.

Before you start

Confirm that the model table contains a Date column and a Serial Number column, and replace 'Table' in the measures with the actual table name. Decide whether duplicate serial numbers should count as separate rows or as one unique serial number.

Solution 1Recommended

Create DAX measures for the oldest date and matching row count

Use these measures when every row on the earliest date should be counted, while keeping the result blank if no case date exists.

MIN returns the earliest Date value in the current filter context. The blank check prevents a missing date from being treated as zero in date arithmetic.

Create the oldest-date measure

In Power BI Desktop, open the Modeling tab, select New measure, and enter: Oldest Case := MIN('Table'[Date])

Create the Weeks Behind measure

On the Modeling tab, select New measure and enter: Weeks Behind := IF(ISBLANK([Oldest Case]),BLANK(),DIVIDE(TODAY()-[Oldest Case],7))

Count rows on the earliest date

Select Modeling > New measure and enter: In Queue := VAR MinDate = [Oldest Case] RETURN IF(ISBLANK(MinDate),BLANK(),CALCULATE(COUNTROWS('Table'),'Table'[Date]=MinDate))

Add the measures to a visual

In the Fields pane, select a table or chart and add Oldest Case, Weeks Behind, and In Queue to the visual Values area to verify the results.

Create DAX measures for the oldest date and matching row count

Blank dates are handled safely: The IF(ISBLANK(...)) condition prevents TODAY() minus a missing date from producing an incorrect, very large Weeks Behind value.

Solution 2

Count distinct serial numbers on the minimum date

Use this variation when duplicate serial numbers on the earliest date should be counted only once.

Free Microsoft Office alternative

Use WPS Spreadsheet for Date-Based Counts and Queue Tracking

Power BI and Power Pivot DAX measures require Microsoft's data-modeling engine, so WPS Office does not execute these DAX formulas directly. For spreadsheet-based date calculations, counts, and queue tracking, WPS Spreadsheet provides a free, lightweight alternative with a familiar interface, strong Excel format compatibility, and easy migration for standard worksheet data.

1. Open the source workbook: Install WPS Office, open WPS Spreadsheet, and select Open to load the XLS or XLSX file containing case dates and serial numbers.
2. Calculate the minimum date: Use a worksheet formula such as =MIN(DateRange) in a summary cell, replacing DateRange with the actual date range.
3. Count matching records: Use COUNTIF or COUNTIFS against the minimum-date cell to count records associated with the earliest date, and use a conditional formula to leave the result blank when no date exists.

Create spreadsheet formulas for minimum dates, conditional counts, and blank-safe elapsed-week calculations.Work in a free, lightweight spreadsheet interface familiar to Microsoft Excel users.Open and save Microsoft Excel XLS and XLSX files with broad format compatibility.Migrate ordinary case-date and serial-number worksheets without rebuilding the entire spreadsheet workflow.

microsoft office alternative - wps office

Frequently Asked Questions

How do I return a blank Weeks Behind value when the oldest date is missing?

Create the measure as Weeks Behind := IF(ISBLANK([Oldest Case]),BLANK(),DIVIDE(TODAY()-[Oldest Case],7)). The ISBLANK check stops DAX from treating a missing date as zero.

What is the DAX formula for finding the earliest case date?

Use Oldest Case := MIN('Table'[Date]). Replace 'Table' with the name of your model table and Date with the actual date column.

How can I count only rows that match the earliest date?

Use a variable for the minimum date and filter the table in CALCULATE: In Queue := VAR MinDate = [Oldest Case] RETURN IF(ISBLANK(MinDate),BLANK(),CALCULATE(COUNTROWS('Table'),'Table'[Date]=MinDate)).

How do I count unique serial numbers instead of all rows?

Replace COUNTROWS('Table') with DISTINCTCOUNT('Table'[Serial Number]) inside the same CALCULATE expression. This prevents duplicate serial numbers on the earliest date from being counted multiple times.

Why might the minimum-date count change when I use slicers?

MIN and CALCULATE normally respect the current filter context. Review the visual, page, and report filters, and use ALL or REMOVEFILTERS only when the earliest date must be calculated across all records rather than the current selection.

Excel & Data Problems