Nested measures both modifying filter context

[scottsen]

Hi Folks! My turn for a question

I would like to know if there is a consistent/knowable criteria for evaluating the order which filters get applied.

Example...

We have some base measure for "opportunity revenue" that calculates the revenue for all customers... That meet some criteria which is "date-sensitive". It is grabbing the last date in the filter context and using it to do a lookup in a profitability table to grab a [ProfitBucket] value... if that is "0" they are an "opportunity".

Opportunity_Revenue :=
CALCULATE (
    [Revenue],
    FILTER (
        Customers,
        CALCULATE (
            VALUES ( Profitability[ProfitBucket] ),
[COLOR="#FF0000"]            LASTDATE ( Calendar[Date] )[/COLOR]
        )  = 0
    )
)

However, if I now (naively?) write a measure that uses that base measure:

Opportunity_Revenue_YTD :=
CALCULATE (
    [[B]Opportunity_Revenue[/B]],
    VALUES ( Calendar[Year] ),
    FILTER (
        ALL ( Calendar ),
        Calendar[KW ID] <= [COLOR="#FF0000"]MAX ( Calendar[KW ID] )[/COLOR]
            && Calendar[KW ID] >= [First Week Id]
    )
)

It becomes... confusing.

Is the Calendar[Date] referred to in the base measure, the original filter context, or the one modified by the FILTER() in my 2nd measure?
or...
Is the Calendar[KW ID] referred to in the 2nd measure, or original filter context, or the one modified by the LASTDATE() in the 1st measure?

[Note: This was probably a dumb example cuz it might accidentally work... I should have used a PriorYear related measure, but I'm too lazy to fix it now, and the question is still valid'ish ]

 

[marco.russo]

The Opportunity_Revenue_YTD measure sets a filter context to a set of dates between [First Week Id] and [KW ID], within the currently selected years.
Then it executes the Opportunity_Revenue measure within such a filter context, so for each customer the Profitability[ProfitBucket] is evaluated within the last day available in the set of dates defined in the previous filter context.
Does it make sense to you, now?

 

[scottsen]

Thanks, Marco.

Ya... what is weird is what you are telling me is that it expects "just like you would expect". Which is not what I expected

 

[marco.russo]

What is the behavior you would expect?

 

[scottsen]

I was just making a joke... that I was trying to make it more complicated than it really was