Forum Discussion

DonRitchie's avatar
DonRitchie
Frequent Visitor
11 months ago
Solved

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

Hi there! I need to create a table that records every day a vehicle is in service.   My source data may look like: VehicleID Date Rented Date Returned abc 11/11/25 11/13/25 abc 11...
Need Help
Ahmedx's avatar
Ahmedx
Icon for Super User rankSuper User
11 months ago

pls try in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRMjTUAyIjAyNTEMcYzonVgaswRlZhik2FObIKCxQVKalpGLYgqYgFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [VehicleID = _t, #"Date Rented" = _t, #"Date Returned" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"VehicleID", type text}, {"Date Rented", type date}, {"Date Returned", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([Date Rented],Duration.Days([Date Returned]-[Date Rented])+1,#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom",{"VehicleID", "Custom"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"