Using DATEADD() and other Date Tips for Filtering Data in Power BI

While working with Dates in DAX, you may have come across an interesting behavior when working with the DATEADD() function. If have used this function in SQL, you have a good idea of how the function works and the parameters you pass to it in order to shift a date forward or backwards. However, this function behaves slightly different in DAX. The main difference is in the parameters passed to the function. Let’s take a look:

SQL Syntax
DATEADD (datepart, number, date )

DAX Syntax 
DATEADD(dates, number_of_intervals, interval)

In SQL, we specify what period we want to shift using datepart; how much we want to shift using number; and to what date using date.

In DAX, we specify to what date using dates; how much we want to shift using number_of_intervals; and what period we want to shift using interval.

The most important distinction between these two functions is the what date parameter. While we can use any expression that resolves to a date/datetime in SQL, when using  DAX we must use an existing date column. In addition to that, that date column must contain the date that you are shifting to in order to return a value, otherwise you will get a BLANK result.

Scenario 1 – Determine Yesterday

Let’s say I want a report that automatically filters my data to show me yesterday’s sales. To do this, I need to compare the dates in my dataset to today’s date. Since I need today’s date to be dynamic (so that it shifts automatically each day in my report), I would be tempted to write something like this:

DAX
[Today's Date] = Today()
  --I need this since the first parameter in DATEADD() 
  --requires me to have a Dates column

Is Yesterday = 
   if( Sales[SalesDate] = DATEADD(Sales[Today's Date],-1,DAY), 1, 0)

While the syntax is correct and I won’t get any errors, I also won’t get any dates filtered to Yesterday. The reason being, “yesterday” doesn’t exist in the dates column (“[Today’s Date]”) that I am passing into the DATEADD() function.

The DATEADD() function requires that the date you are shifting to exists in your DATES parameter. So if you have a field that defaults to Today, it will never find any other date using DATEADD().

Solution

An easy solution to this is to subtract 1 from [Today’s Date] without using the DATEADD() function:

Is Yesterday =
    if( Sales[SalesDate] = Sales[Today's Date]-1, 1, 0)

 

Scenario 2 – Determine Current Month

Now let’s say I want to create a flag for Current Month based on today’s date again so that the filter changes dynamically each month.

Following what we learned about the DATEADD() function, we know we have to use a different approach. Here’s how I would write the expression:

Is Current Month = 
if( YEAR(Sales[SalesDate]) = YEAR(Sales[Today's Date]) 
    && MONTH(Sales[SalesDate]) = MONTH(Sales[Today's Date]), 
    1, 
    0
   )

I’m comparing both the Year and the Month Number of the date values. Comparing only the month would incorrectly include last year’s data in the Is Current Month filter.

Scenario 3 – Determine Prior Month

This is where things start getting a bit more tricky. In order to get the prior month from Today’s date, I need to shift my date back one month (I really want to use DATEADD() here, but we know where that will lead us…). Subtracting from Today’s Date will shift my date by days, which would not yield consistent results.

There are 2 ways in which we can achieve our results here:

1. Nested Ifs.
We can get the Month from Today’s Date and subtract 1 to get the previous month. This will work for all months except January. To make sure that we go back to December in this case, we need to have an If statement to go to 12 instead of 0 and subtract 1 from the year as well.

Here’s what the syntax would look like:

is Prior Month v1 = 
  if( MONTH(Sales[Today's Date])=1, 
        if( MONTH(Sales[SalesDate]) = 12 
            && YEAR(Sales[SalesDate]) = YEAR(Sales[Today's Date])-1, 1, 0) ,
        if( MONTH(Sales[SalesDate]) = MONTH(Sales[Today's Date])-1 
            && YEAR(Sales[SalesDate]) = YEAR(Sales[Today's Date]), 1, 0)
     )

 

2. Using EDATE() function
DAX has a function called EDATE(<start date>, months) that takes a date value (read: not a date column) and shifts it a specified number of months. With this approach, we don’t have to worry about checking for January and having nested If statements, so the code looks a bit cleaner.

Here’s what the syntax would look like:

Is Prior Month v2 = 
  if( MONTH(Sales[SalesDate]) = MONTH(EDATE(Sales[Today's Date],-1)) 
      && YEAR(Sales[SalesDate]) = YEAR(EDATE(Sales[Today's Date],-1)) , 
      1, 
      0
    )

I’ve published a Power BI Report so you can see the functionality in action. If you want to download a copy of this report to see all the calculations click here.