topic Re: Count Open Orders with missing date records in DAX Commands and Tips

Count Open Orders with missing date records

joshua1990 — Sun, 08 May 2022 14:27:37 GMT

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?

Re: Count Open Orders with missing date records

tackytechtom — Sun, 08 May 2022 15:28:08 GMT

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/

Re: Count Open Orders with missing date records

joshua1990 — Sun, 08 May 2022 16:12:27 GMT

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

Re: Count Open Orders with missing date records

tackytechtom — Sun, 08 May 2022 17:47:09 GMT

Hi joshua1990 ,

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

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

Re: Count Open Orders with missing date records

tamerj1 — Sun, 08 May 2022 19:09:40 GMT

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' ) )

Re: Count Open Orders with missing date records

Anonymous — Wed, 11 May 2022 02:26:27 GMT

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.