DAX Measure - Extrapolate last Date for COUNT

Question

I have a table that contains 3 columns:

• Order
• Date
• State

Each row / record shows if the state was changed:

Now I would like to calculate the number of order that are below state 3 for each date in the calendar. In the example above you can see there is nor entry for order 100 for 07.01.2022. But for this date the order is still below 3 as you can see in the record before.

How would you do that?

Answer 1

I think this might be better to do in PowerQuery.

Suppose the table mentioned in your question is order_state:

let
    Source = Table.FromRows(
    {
        {100, 1, "1/1/2022"},
        {100, 2, "1/5/2022"},
        {100, 3, "1/8/2022"},
        {101, 1, "1/5/2022"},
        {101, 2, "1/6/2022"},
        {101, 3, "1/7/2022"},
        {102, 1, "1/7/2022"},
        {102, 2, "1/9/2022"},
        {102, 3, "1/10/2022"}
    },
    {"Order", "Status", "Date"}
    ),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}})
in
    #"Changed Type"

Then I believe you can calculate what you want with a query like this:

let
    start_date = List.Min(order_state[Date]),
    end_date = List.Max(order_state[Date]),
    days = Duration.Days(end_date - start_date),
    dates = Table.FromList(List.Dates(start_date,days,#duration(1, 0, 0, 0)), Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    joined = Table.AddColumn(
                    dates,
                    "order_state_rows",
                    (current) => Table.RowCount(
                                    Table.Distinct(
                                        Table.SelectColumns(
                                            Table.SelectRows(
                                                order_state,
                                                (row) => (row[Date] <= current[Date] and row[Status] <> 3)
                                            )
                                        ,{"Order"}
                                        )
                                    )
                                )
            )
in
    joined

Which gives this result: