Active Orders

Solution: Active Orders

Relationships, filter context, row context, context transition, measures. Any DAX programmer knows that mixing all these concepts together makes the formulas somewhat complex. In this puzzle, we ask you to author a measure that proves to be a bit challenging, due to the interactions of all these topics.

There are many different solutions to this example, we provide two variations that we like most, describing the reasons why they are our best choice. The basic idea is to compute all orders started before [StartDate] and ended after [LastDate]. For this reason, we group orders by StartDate and EndDate, reducing the number of iterations performed by the filter that check whether the dates are within the range we want to use.

OpenOrders :=
AVERAGEX (
    DATESBETWEEN ( 'Date'[Date], [FirstDate], [LastDate] ),
    SUMX (
        FILTER (
            CALCULATETABLE (
                ADDCOLUMNS (
                    SUMMARIZE ( Orders, Orders[StartDate], Orders[EndDate] ),
                    "NumOfOrders", CALCULATE ( COUNTROWS ( Orders ) )
                ),
                ALL ( 'Date' )
            ),
            AND ( Orders[StartDate] <= 'Date'[Date], Orders[EndDate] >= 'Date'[Date] )
        ),
        [NumOfOrders]
    )
)

You might wonder whether scanning all the combinations of StartDate and EndDate for every date in the range is a smart idea. You can create a filter to group only the orders that are within the desired range, but this might result in a slower operation, depending on distribution of data. In this example, the following formula is slower, but you might find that this approach is faster in other conditions.

OpenOrders 2 :=
AVERAGEX (
    DATESBETWEEN ( 'Date'[Date], [FirstDate], [LastDate] ),
    SUMX (
        CALCULATETABLE (
            ADDCOLUMNS (
                SUMMARIZE ( Orders, Orders[StartDate], Orders[EndDate] ),
                "NumOfOrders", CALCULATE ( COUNTROWS ( Orders ) )
            ),
            ALL ( 'Date' ),
            FILTER ( ALL ( Orders[StartDate] ), Orders[StartDate] <= 'Date'[Date] ),
            FILTER ( ALL ( Orders[EndDate] ), Orders[EndDate] >= 'Date'[Date] )
        ),
        [NumOfOrders]
    )
)

Download