DAX Power BI: Difference in the use of DATEADD and PARALLELPERIOD
Consider the date functions DATEADD and PARALLEDPERIOD, which create a column of dates shifted relative to the original dates.
These functions work with date logic and require a column of dates to be based on a calendar as input, and these functions are embedded in the CALCULATE function as a filter parameter.
Why do we need functions to work with date logic?
So that we can compute our measure not within the current date context, but within a modified context.
The basic functions for working with dates are DATEADD and PARALLELPERIOD. Both functions create a column of dates shifted forward or backward. They differ in that the DATEADD function returns a column limited to two dates, while the PARALLELPERIOD function returns a column of dates limited to periods.
Let’s look at the data model
Let’s calculate the amount by sales and use the measure to do so
Now let’s create a new measure that will calculate the amount of sales carried forward to some period.
Let’s consider the DATEADD function. The first parameter of this function is the date column. The second parameter is the interval we need to return. The third parameter is the interval (day, month, year and so on).
Here we have specified the second parameter as 0. This means that the shift will take place for 0 months, i.e. we actually take the current month.
If we set -1 as the second parameter, the data will be returned for the last month.
Now let’s look at the PARALLELPERIOD function
Now we see the same result as the DATEADD function. In this example, these functions work the same way. But they differ in that PARALLELPERIOD returns a specific period. Here it returned the last month. And DATEADD has shifted everything by one month.
You will notice the difference between these functions if we go down to dates
We see that the PARALLELPERIOD function in all dates sets the value for the last period — for the last month.
But if PARALLELPERIOD is replaced by DATEADD, then DATEADD will return not the last period, but the values shifted by one month. That is, where there was a value for October 1, we will see a value for September 1, and so on.
If we want to take the interval “from the future”, we will write 1 instead of -1. That is, we will take values from the next month
Don’t forget to subscribe to
and join our Power BI community
