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.

Scenario

The data model is very simple: you have orders and date. Each order is received at ReceiveDate and processed between StartDate and EndDate. The relationship between orders and dates is based on ReceiveDate. EndDate is always greater than StartDate, which is always greater than ReceiveDate.
Puzzle Context Transition 1

The Challenge

You want to compute the number of orders which are being worked at any given date, i.e. the orders already started (given date greater than StartDate) and not yet finished (given date less than EndDate). At the month level the number shown need to be the average of active orders of each day. At the grand total, you want to show the number of active orders as of today.

To achieve this goal, you already have two measures defining the boundaries of your calculation. The business model requires you to use these two measures to compute the boundaries, as they are used elsewhere in the model and they are likely to change according to what the business might define later:

FirstDate := 
IF ( 
    ISCROSSFILTERED ( 'Date'[Date] ), 
    MIN ( 'Date'[Date] ), 
    TODAY () 
)

LastDate := 
IF ( 
    ISCROSSFILTERED ( 'Date'[Date] ), 
    MAX ( 'Date'[Date] ), 
    TODAY () 
)

At the end, you need to be able to produce a report like this one:
Puzzle Context Transition 2

Hints

The relationship with Date is based on ReceiveDate. You have to read the current context on Date and use this information to restrict, in some way, Orders based on StartDate and EndDate, getting rid of the automatic filtering. In doing that, you have to use CALCULATE and some iterations, creating a somewhat complex interaction between the entities.

Of course, since you need an AVERAGE at the month and year level (but not at the grand total), some more context transitions will happen.

Solution

To see the solution, just press the button below.

View solution