Count Open Orders with missing date records

[joshua1990]

Hi community!

I have a pretty simple table that shows me all status changes for each order:

Order Nr	Status	Status Changed Date
555	A	01.01.2021
555	B	05.01.2021
555	C	15.01.2021
556	A	05.01.2021
556	C	20.01.2021

As you can see there is just a record if a status was changed.

Now I would like to display on a bar chart every day of the current year with number of Orders with a Status <> 'C'.

Bow how?

A simple CALCULATE(DISTINCOUNT(Orders[Orders]), Status <> 'C') will not work since there are no records for each day.

How can this be solved using DAX?

[v-yangliu-msft]

Hi  @joshua1990 ,

Has your problem been solved, if so, you can mark its correct answer as a mark, if not, provide details and we can help you better.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @joshua1990 
If Power query is not an option then another way of doing that using DAX only (must have a date table with or without relationship): https://www.dropbox.com/t/e3B2ky5BjspDINnw

Number of Orders = 
VAR CurrentDate = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUMX (
            VALUES ( Orders[Order Nr] ),
            VAR ActiveOrdersTable =
                CALCULATETABLE (
                    Orders,
                    Orders[Status Changed Date] <= CurrentDate
                )
            VAR StatusCTable =
                FILTER ( ActiveOrdersTable, Orders[Status] = "C" )
            RETURN
            IF (
                COUNTROWS ( ActiveOrdersTable ) > 0 && COUNTROWS ( StatusCTable ) = 0,
                1
            )
        ),
        REMOVEFILTERS ( 'Date' )
    )

  

[tackytechtom · Accepted solution]

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/

[joshua1990]

@tackytechtom : Thanks you so much! Could you please share your PBI File?

[tackytechtom]

Hi @joshua1990 ,

https://www.dropbox.com/s/jz5pk5lhl4d5tn9/FillRowsBetweenDates.pbix?dl=0

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