Power Query - DATEADD fails once current year filter applies

Question

Please help. I don't understand why I have 2 similar data models and one works but the other does not.

I have a fact table with a Calendar Key and data as granular by day by location. I have a calendar dimension table with a Calendar key, each individual date, and month/fiscal year definitions.

Measures are Rev = SUM(FactTable[RevColumn]) then Rev PY = CALCULATE([Rev], DATEADD(CalendarTable[Day_Date], -1, YEAR))

Model 1 in Power BI, when I apply date filters, Rev calculates the total revenue & Rev PY calculates last year's rev for same range. Even if I select all dates that are YTD (which is tedious)

Model 2 in Excel 2016 Power Query, when I apply a year filter, Rev calculates total revenue for YTD in Fiscal Year & Rev PY is blank.

I don't understand what is different and why it would even fail in the first place. Calculate is supposed to override the filter context of the current filters right?

Also: the only "Date" type columns are located in the calendar table so it's not possible for me to select the wrong table.

Answer 1

I ultimately had to ask a professor on Udemy.com. The solution is to mark you calendar dimension table as a "Date Table." Since I'm combining the 2 tables on a whole number field, the time intelligence functions only work when filtering with date columns.

By marking the Calendar table as the Date table, the time intelligence functions know to use any attributes from that table to filter down the date ranges and get and display the appropriate answer.

In Power Pivot view, click the "Design" ribbon, then click the "Date Table" button and the "Set Default" button after that.

This resolved all issues I had.