[SOLVED] How is offset able to go to prior year data without using having to explicitly to remove the filters from Date table?

How is offset able to go to prior year data without using having to explicitly to remove the filters from Date table?

DEFINE
MEASURE DimProduct[CurrentYearSales] = SUM(FactInternetSales[SalesAmount])
MEASURE DimProduct[PreviousYearSales] = CALCULATE(SUM(FactInternetSales[SalesAmount]), OFFSET(-1, , ORDERBY(DimDate[CalendarYear])))
EVALUATE
SUMMARIZECOLUMNS (
    DimDate[CalendarYear],
    "CurrentYearSales", DimProduct[CurrentYearSales],
    "PreviousYearSales", DimProduct[PreviousYearSales]
)

In summarise columns, for each row we calculate the current year and previous year sales.

However in the context of the current year row, how is OFFSET able to go to the prior year without the need to remove the filter from the Date table using either REMOVEFILTERS or ALL?

Solution

According to SQLBI: https://www.sqlbi.com/articles/introducing-window-functions-in-dax/

Because we did not specify the source table, OFFSET automatically generated the table using ALLSELECTED over all the columns specified in ORDERBY.

Relying on the automatic table generation can produce simpler code – at the risk of not finding the desired rows, and of obtaining an inaccurate result. The correct way to express the calculation is to extend the filter context of the source table by using ALL ( ‘Date'[Year] ). Using ALL to ignore the previous filters on year produces the correct result: