Filtering Columns Puzzle

In Power BI Desktop and SQL Server Analysis Services 2016 the SELECTCOLUMNS function allows you to retrieve some of the columns out of a table. This enables you to write set operations and queries in a very simple way. But, as it often happens with powerful functionalities, it hides some complexity, and this puzzle is aimed to test your understanding of SELECTCOLUMNS.

Scenario

You have a canonical star schema.

Filtering Columns 1

In the Date table there is a column indicating whether a day is a special offer day or not. In addition, you know that Saturday, in your specific business, are always to be considered special days. Therefore, you create a measure to compute sales in special days:

SpecialDaySales :=
VAR SpecialDates =
    UNION (
        SELECTCOLUMNS (
            FILTER ( 'Date', 'Date'[SpecialDay] = "Special Offer Day" ),
            "Date", [Date]
        ),
        SELECTCOLUMNS (
            FILTER ( 'Date', 'Date'[Day Of Week] = "Saturday" ),
            "Date", [Date]
        )
    )
RETURN
    CALCULATE ( [Sales Amount], SpecialDates )

This measure works just fine; the challenge is coming in the next step.

The Challenge

You are worried that sales, in the day after a special one, are lower than the average. To double-check it, you create a new measure that computes the sales in the day after a special one. You know that you need a special day and add 1, or any Sunday. Thus, your code looks like this:

AfterSpecialDaySales :=
VAR AfterSpecialDates =
    UNION (
        SELECTCOLUMNS (
            FILTER ( 'Date', 'Date'[SpecialDay] = "Special Offer Day" ),
            "Date", [Date] + 1
        ),
        SELECTCOLUMNS (
            FILTER ( 'Date', 'Date'[Day Of Week] = "Sunday" ),
            "Date", [Date]
        )
    )
RETURN
    CALCULATE ( [Sales Amount], AfterSpecialDates )

Unfortunately, this measure returns wrong results. As you can see in the next figure, it always reports the total sales, which is clearly wrong:

Filtering Columns 2

Can you explain what is happening and, optionally, correct the measure?

Hints

The correct result for AfterSpecialDaySales measure is illustrated in the following figure:
Filtering Columns 3


View solution

Please, use comments here only for discussions about the scenario and do not post a possible solution. Use the comments area in the solution page to post your alternative solutions.
Enclose code in comments with <PRE></PRE> to preserve indentation.