DAX to create a table that records every date between a date range for each item in another field

Hi DonRitchie ,

You can create a custom column in power query on your source data with the following expression:

= List.Dates(
[Date Rented],
Duration.Days([Date Returned] - [Date Rented]) + 1,
#duration(1,0,0,0)
)

Then expand that column to new rows, rename the column as appropriate, then select only the VehicleID and renamed custom column and remove duplicates.

Hope that helps.

Hi DonRitchie,

To generate a row for each day a vehicle is in service, the most efficient and scalable approach is to use Power Query to expand the date ranges between Date Rented and Date Returned. This method avoids complex DAX logic and ensures performance even with larger datasets.

follow the example:

Here the code I reproduced you case:

let
    // Sample source data
    Source = Table.FromRows({
        {"abc", #date(2025,11,11), #date(2025,11,13)},
        {"abc", #date(2025,11,13), null},
        {"abc", #date(2025,11,15), null},
        {"abc", #date(2025,11,17), #date(2025,11,18)},
        {"def", #date(2025,11,11), #date(2025,11,18)}
    }, {"VehicleID", "Date Rented", "Date Returned"}),

    // Replace nulls in 'Date Returned' with a fallback date
    ReplaceNulls = Table.TransformColumns(Source, {
        {"Date Returned", each if _ = null then #date(2025,12,31) else _, type date}
    }),

    // Add a column with a list of dates between rented and returned
    AddDateList = Table.AddColumn(ReplaceNulls, "DateInService", each List.Dates(
        [Date Rented],
        Duration.Days([Date Returned] - [Date Rented]) + 1,
        #duration(1,0,0,0)
    )),

    // Expand the list of dates into individual rows
    ExpandDates = Table.ExpandListColumn(AddDateList, "DateInService"),

    // Remove original date columns
    RemoveOriginalColumns = Table.RemoveColumns(ExpandDates, {"Date Rented", "Date Returned"}),

    // Remove duplicate rows
    RemoveDuplicates = Table.Distinct(RemoveOriginalColumns)
in
    RemoveDuplicates

✅ If this answer resolved your issue, please mark it as correct to help other members of the community.

Hi DonRitchie ,

You can generate the table like the 2nd one by writing a calculated table measure like below:

ServiceDays = 
DISTINCT (
    SELECTCOLUMNS (
        GENERATE (
            'SourceData', // <-- Change this to your table's name
            CALENDAR (
                'SourceData'[Date Rented],
                'SourceData'[Date Returned]
            )
        ),
        "VehicleID", 'SourceData'[VehicleID],
        "DateInService", [Date]
    )
)

The resultant output is the same as your 2nd table:

I have attached an example pbix file for your reference.

Best regards,