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

13-05-2019  0 Comment(s)

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:

[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().


Comment Here


No Comments to Show