Forum Discussion

Charcoal55's avatar
Charcoal55
Regular Visitor
1 year ago
Solved

DATEADD and MAX?

Hi,
I understand that the below filter function returns a table containing all dates in the date table and where the latest date in that table is defined by the filter context. Thus, if the date table contains all dates between 2000 and 2024 and the filter context is 2023, the below function will return a table with all dates between 2000-2023.
 
    FILTER(
        ALL(Dates),
        Dates[Date] <= MAX(Dates[Date])
    )

However, if I want (for the very same filter context, that is, 2023) to return a table will all dates between 2000-2022, I guess I can modify the above function so it instead reads:
 
FILTER(
        ALL(Dates),
        Dates[Date] <= MAX(Dates[Date]) - 365
    )
 
But there should be some more elegant solution using DATEADD or SAMEPERIODLASTYEAR instead, something like below but I don't get it to work. I assume this is super simple, but for now I got stuck...
 
FILTER(
        ALL(Dates),
        Dates[Date] <= DATEADD(MAX(Dates[Date]), -1, YEAR) 
    )
  • FreemanZ's avatar
    FreemanZ
    1 year ago

    hi Charcoal55 ,

     

    try like:

     

    FILTER(

            ALL(Dates),

            Dates[Date] <= EDATE(MAX(Dates[Date]), -12) 

        )

     

    EDATE is not time intelligence function and thus less picky.

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    1 year ago

    hi Charcoal55 ,

     

    try like:

     

    FILTER(

            ALL(Dates),

            Dates[Date] <= EDATE(MAX(Dates[Date]), -12) 

        )

     

    EDATE is not time intelligence function and thus less picky.

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Charcoal55 , Formula is correc

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

     

    Just ensure that

    The Dates table is properly related to other tables in your data model.
    The filter context is correctly applied.
    There are no syntax errors or other issues in the broader DAX expression where this formula is used.

  • FarhanJeelani's avatar
    FarhanJeelani
    Super User
    1 year ago

    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.

    • Charcoal55's avatar
      Charcoal55
      Regular Visitor
      1 year ago

      The problem is that I get the following error message when I am trying the formula you suggest:

      "The first argument to 'DATEADD' must specify a column."

      I interpret it that since MAX returns a scalar value and that is not acceptable by DATEADD because it requires a column as it's first argument.