DAX Power BI: DATESBETWEEN & DATESINPERIOD

With the DATESBETWEEN and DATESINPERIOD functions, you can limit a column of dates, or take specific periods.

Let’s look at these functions in practice.

The DATESBETWEEN function can take as its first argument a date from the calendar, as well as from a regular table. This is because it works with any date.

The first parameter is the required column with dates.

The result is a column of dates. Then we can use it in the CALCULATE function, for example, as a filter.

The second function under consideration is DATESINPERIOD. It is exactly the same in parameters, but in the third parameter the necessary number of periods from the start date is inserted, and the last one — in what form the period will be expressed (days, months, years, and so on).

Let’s see how you can use these functions in real projects. To do this, let’s create a measure that will count the amount of sales for the current day and the previous day. Let’s do it with the function CALCULATE, where we paste in the above functions. To get the previous value, we can take the date and subtract 1 from it. Thus we get the previous date. To do this, we will use function FIRSTDATE. And with the function LASTDATE we will get the last present value.

You can see that in the TotalCurrentAndPreviosDays column, each value is equal to the sum of the current and previous values of the Total column.