How to do Date calculations in DAX

Moving back and forth in time is a common task for Time Intelligence calculations in DAX. We have some excellent functions; one of the most useful is DATEADD. Let’s take a detailed look at it.

What is it for?

I have shown the usefulness of the DATEADD() function in one of my past articles:

Explore variants of Time Intelligence in DAX

But sometimes, we want to do other stuff.

For example, we have a fixed date and want to move it back or forward in time.

If we want to do it for some days, it’s very easy:

For example, I want to move the date 2025/01/15 10 days into the future:

Figure 1 - Moving a date ten days into the future (Figure by the Author) — Figure 1 – Moving a date ten days into the future (Figure by the Author)

I must add the curly brackets as EVALUATE expects a table, and these brackets create a table from the output of the expression.

But what when we want to do this for months, quarters, years, or weeks?

Look at some use cases.

The DATEADD() function seems the obvious choice for the function to perform such tasks.

Before working with Dax, I worked with T-SQL for SQL Server. The T-SQL DATEADD() function could be used precisely for this.

OK, let’s give it a try to go back two months, starting with 2025/01/25:

Figure 2 - Error message when we try using DATEADD() with a simple Date variable (Figure by the Author) — Figure 2 – Error message when we try using DATEADD() with a simple Date variable (Figure by the Author)

This doesn’t work, as I passed a Date variable. Even though it looks intuitive, it doesn’t work.

The documentation explains that the first parameter must be a column containing dates.

Therefore, we must persuade the engine to think that we pass a date column to the function.

One way to do it is by using TREATAS():

DEFINE
 VAR MyDate = DATE(2025, 1, 25)

EVALUATE
 DATEADD(
  TREATAS({MyDate}
   ,'Date'[Date]
   )
  ,-2
  ,MONTH)

Notice that we don’t need the curly brackets, as DATEADD() returns a table with one column and, in this case, one row.

When executing this query, DATEADD() thinks that it receives a value from the [Date] column from the Date table as a parameter and performs the calculation as expected:

Figure 3 - Using DATEADD() with a fixed date a TREATAS() (Figure by the Author) — Figure 3 – Using DATEADD() with a fixed date a TREATAS() (Figure by the Author)

Another way to perform this task is by using the Date table and passing the Variable with the date as a filter:

DEFINE
    VAR MyDate = DATE(2025, 1, 25)

EVALUATE
    CALCULATETABLE(
                DATEADD('Date'[Date]
                        ,-2
                        ,MONTH)
                ,'Date'[Date] = MyDate
                )

The result is the same as before, and there is no notable difference in the execution time (Between 7 and 10 ms).

Again, when looking at the documentation on DAX.guide, they use the second approach for doing the job.

We can change the second expression to get one single value:

DEFINE
    VAR MyDate = DATE(2025, 1, 25)

    VAR Result =
        CALCULATE(DATEADD('Date'[Date]
                        ,-2
                        ,MONTH)
                ,'Date'[Date] = MyDate
                )

EVALUATE
    { Result }

These three approaches are almost identical functionally. However, the difference is that we can use these two approaches differently.

For example, we want to calculate the Online Sale for the day 2 months before 2025/01/25.

Here is the first approach:

DEFINE
    VAR MyDate = DATE(2025, 1, 25)

    VAR TargetDate =
                DATEADD(
                        TREATAS({MyDate}
                            ,'Date'[Date]
                            )
                        ,-2
                        ,MONTH)

EVALUATE
{
    CALCULATE([Sum Online Sales]
            ,TargetDate
            )
            }

And here is the second one:

DEFINE
    VAR MyDate = DATE(2025, 1, 25)

    VAR TargetDate =
                CALCULATE(DATEADD('Date'[Date]
                        ,-2
                        ,MONTH)
                ,'Date'[Date] = MyDate
                )

EVALUATE
{
    CALCULATE([Sum Online Sales]
            ,'Date'[Date] = TargetDate
            )
            }

The result of the expression is not essential here.

But do you notice the difference?

The first expression uses DATEADD to get a table aligned with the [Date] column of the Date table. I can directly use this table as a Filter in CALCULATE(). It will follow the filter’s Lineage (the origin) and can be applied directly to the Data model.

The second expression generates a single value and loses the lineage to the Date table.

Therefore, we must use the = operator to filter the Date table.

Again, both approaches return identical results and are equivalent performance-wise.

What about weeks?

Calculating with weeks is not as easy as using DATEADD(), as this function supports only Days, Months, Quarters, and Years.

Fortunately, weeks always have 7 days. Therefore, we can move back and forth by multiplying 7 days by the number of weeks we want to move the start date.

For example, I want to go back three weeks:

7 x 3 = 21 days: 2025/01/25 to 2025/01/04:

Figure 4 - Going back three weeks by subtracting 21 days from the starting date (Figure by the Author) — Figure 4 – Going back three weeks by subtracting 21 days from the starting date (Figure by the Author)

A more elaborate pattern for the same task is this:

DEFINE
    VAR MyDate = DATE(2025, 1, 25)

    VAR DayOfWeek = CALCULATE(MIN('Date'[DayOfWeek])
                                ,'Date'[Date] = MyDate
                                )

    VAR WeekIndex = CALCULATE(MIN('Date'[WeekIndex])
                                ,'Date'[Date] = MyDate)

EVALUATE
    { CALCULATE(MIN('Date'[Date])
                ,REMOVEFILTERS('Date')
                ,'Date'[DayOfWeek] = DayOfWeek
                ,'Date'[WeekIndex] = WeekIndex - 3
                )
        }

Here, I leverage two columns I commonly add to my Date tables:

Day of Week
WeekIndex: This one counts the number of weeks starting from the week of the last data refresh. Today, it’s zero, the week before the current is -1, and the week after the current week is 1, etc.

Now, I can use them to perform a dynamic calculation based on these columns.

Interestingly, this version of the query’s performance is not slower than before.

The result is still the same:

Figure 5 - More complex approach to go back three weeks (Figure by the Author) — Figure 5 – More complex approach to go back three weeks (Figure by the Author)

But why should I use a more complex approach when subtracting some days lead to the same result?

Well, it depends on what you want to achieve. I experienced some scenarios where a more complex approach leads to more possibilities, and it can be used more generically.

In this specific case, the first approach works very well.

Conclusion

Manipulating dates is one of the main tasks when analyzing or visualizing data.

The DATEADD() function is a crucial part of the DAX language’s toolset, and it’s important to know how it works and how it can be used.

There are three main points with this function:

How the input date-parameter must look like
How can I manipulate a date to work with DATEADD()
How can I use the output of DATEADD() in Measures

I tried to show you all of them in this piece. I hope that you found this helpful.

I will show you more complex scenarios in an upcoming piece, where using such additional columns can save you much time – both development and execution time.

In the meantime, you can start incorporating these techniques into your work.