Count Open Orders with missing date records

Hi joshua1990 ,

Maybe something like this? 🙂

The secret is to "unfold" / "expand" the dates in between the status changes. So instead of having your table, I would try to change the grain and get something like this one (just for the Order Nr = 555):

With such a table, Power BI / DAX will have it easy to display the Number of Orders per day (just create a measure with a filter on status <> "c" ) I did the transformation in PQ, where I first created an additional date column which is a bit like an "End Date". With a StartDate and EndDate column grouped by OrderNr it is pretty easy to create the rows between them.

Here the code for the advanced editor. Note, my table is called FillRowsBetweenDates.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjU1VdJRcgRiA0M9IDIyMDJUitWBSTiBJEyxSDgDsSG6hBnMKGwSIB1GBgiJWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Nr" = _t, Status = _t, #"Status Changed Date" = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Order Nr"}, {{"Grouping", each _, type table [Order Nr=nullable number, Status=nullable text, Status Changed Date=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ( [Grouping], "Index", 1 )),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Order Nr", "Status", "Status Changed Date", "Index"}, {"Order Nr", "Status", "Status Changed Date", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "JoinIndex", each [Index] - 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Order Nr", "Index"}, #"Added Custom1", {"Order Nr", "JoinIndex"}, "Added Custom1", JoinKind.LeftOuter),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"Status Changed Date", "Start Date"}}),
    #"Expanded Added Custom1" = Table.ExpandTableColumn(#"Renamed Columns", "Added Custom1", {"Status Changed Date"}, {"Status Changed Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Custom1",{"Index", "JoinIndex"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Order Nr", Int64.Type}, {"Status", type text}, {"Start Date", type date}, {"Status Changed Date", type date}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type", "Custom", each if [Status Changed Date] = null then null else { Number.From ( [Start Date] ) ..Number.From ( [Status Changed Date] ) -1 }),
    #"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1",{{"Custom", type date}})
in
    #"Changed Type1"

Let me know if this helps or if you get stuck somewhere 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/