how does filter context work

[Jeanxyz]

I'm studying The Definitive Guide to DAX and get confused on how filter context works.

In the measure below (p193), which filter context is applied to values('Date'[Date])? The external filter context or the new context created by filter(all('Date'[Date])? Why? 

Similarly, which filter context is applied to YEAR ( 'Date'[Date] ? The external filter context or the new context created by filter(ll('Date'[Date])?

sales amount_1M test =
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Date'[Date] ),
        CONTAINS (
            VALUES ( 'Date'[Date] ),
            'Date'[Date], DATE ( YEAR ( 'Date'[Date] ), MONTH ( 'Date'[Date] ) - 1, DAY ( 'Date'[Date] ) )
        )
    )
)

According to the textbook, the measure should work similarly as Dateadd(). So I have written the measure below. 

sales amount_1M = calculate([Sales Amount],dateadd('Date'[Date],-1,month))

However, when I compared the two measures, the results are quite different in some product categories.  Why the 1st measure didn't work as exprected?

Here is the sample file. https://www.dropbox.com/s/1ek10256f19lnaw/Contoso.pbix?dl=0

[v-easonf-msft · Accepted solution]

Hi, @Jeanxyz 

The same logic is difficult to reproduce in a general dax formula( without considering the number of days), which has been mentioned in 'The Definitive Guide to DAX'.

The following formula is similar, but not completely equivalent.

TREATAS(
    SELECTCOLUMNS(
        CALCULATETABLE(DISTINCT('Date'[Date])),
        "Date",DATE(
            YEAR('Date'[Date]),
            MONTH('Date'[Date])-1,
            DAY('Date'[Date])
        )
    ),
    'Date'[Date]))

This formula will work normally only when the target month and the current month have the same number of days, otherwise several days(1day=31-30, 2days=29-31,3days=28-31 )  will be lost or included.

Best Regards,
Community Support Team _ Eason

[Ashish_Mathur]

Hi,

I am not sure of what you want but i think you want to calculate the sales of the previous month.  Try this measure

=calculate([Sales Amount],previousmonth('Date'[Date]))

Please ensure that you drag Year and Month from the Date table to your visual and there is a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of your Date table.

Hope this helps.

[lbendlin]

Your first measure is missing the transition from January to December of the prior year. The book has that shown correctly.

The applied filter context is ALL(Date[Date]).

[bcdobbs]

@lbendlin is totally correct that the expression doesn't handle dates in January going back to December.

However I think the answer to your other question about context needs expanding.

ALL(Date[Date]) is only providing the base table for the iterator. FILTER doesn't modify the filter context (only calculate expressions can do that). Therefore VALUES(Date[Date]) comes from the external context.  YEAR(Date[Date]) comes from the row context of the iterator.