DATEADD and MAX?

Hi Charcoal55 

You're right in thinking that DATEADD can be used to adjust the filter context, but there’s a small issue with how you're applying it in the FILTER function.

In your case, you want to return all dates from 2000 to 2022 when the filter context is for 2023. You can achieve this by shifting the latest date in the context (which is defined by MAX(Dates[Date])) back by one year. Here's the correct approach using DATEADD:

FILTER(
    ALL(Dates),
    Dates[Date] <= DATEADD(MAX(Dates[Date]), -1, YEAR)
)

Explanation:

• MAX(Dates[Date]): This gets the latest date in the current filter context (e.g., 2023).
• DATEADD(MAX(Dates[Date]), -1, YEAR): This shifts that latest date back by one year, so if the filter context is 2023, it becomes 2022.
• ALL(Dates): This removes any filters that might be applied to the Dates table, ensuring that you're looking at the entire range of dates.
• Dates[Date] <= DATEADD(...): Finally, the FILTER ensures that you're only including dates up to (and including) the adjusted date (2022 in this case).

This approach is cleaner and avoids hardcoding the date offset (e.g., -365). The DATEADD function handles the exact date subtraction, which accounts for leap years and other complexities.

Please mark this as solution if it helps. Appreciate Kudos.