DATESBETWEEN function in Power bi with variables
datesbetween
DATESBETWEEN is a very helpful time intelligence DAX function that enables you to get a table with a column of dates between a specific range. This function can come in handy for a number of calculations, for instance, seeing the sales on each day for a week. This guide will help you get the most of this function and also enable you to understand the best use cases.
Syntax
The syntax of this valuable function is straightforward and has three parameters:
DATESBETWEEN (<dates>, <start_date>, <end_date>)
In the above-mentioned syntax, the <dates> is the reference to the date column of the table that you wish to use, <start_date> and <end_date> are hard-coded or dynamic fields that can be used to set the range. Both of these parameters are inclusive, for instance, if May 1st is the start_date parameter then the data from this date will be included in the results. In case one of the parameters is BLANK then this function utilized the latest or earliest value from the column mentioned in the first parameter.
Example
Assume that you have a table containing dates and the amount of lemonade a kid sold on those specific dates. Let us say that you want to retrieve the sum of the lemonades sold between January 1st to January 15th using the DATESBETWEEN functions. Here is how you can do it:
Table A: Contains a list of dates and lemonade sold
To calculate the sum of total sold for the first half of January, we can use this expression:
TotalSold = CALCULATE (SUM (Lemonade [LemonadeSold]), DATESBETWEEN (Lemonade [Date], DATE (2020,1,1), DATE (2020,1,15)))
Suppose you leave the last parameter blank; this function will give you the total lemonade sold for the month of January as it uses the latest date to retrieve data if the end_date is not mentioned.
Hence, this function is useful when you only know either the beginning or end date of the date range. You do not have to hardcode the date values. You can use dynamic date values as shown in the example below:
In the example above, the function will retrieve the sum of lemonades sold from the start date to ten days after. The above example sheds light on the best use cases of this time intelligence function and how you can incorporate it into your dashboards.
Datesbetween and Datesinperiod
Both DATESBETWEEN AND DATESINPERIOD function can be used to calculate different measures between specific ranges. But the latter is suitable if you have regular intervals in your date column.
The DATESINPERIOD has four parameters; reference to date column, start date, number of intervals, and interval. Interval enables you to mention the type of interval, for instance, month, day, etc. Number of intervals allow you to go back or forward in time, for instance, 2 combined with DAY as interval will get you the data from start date to two days later. You can use these functions interchangeably combined with other time intelligence functions. Both of these functions are highly valuable when passed to CALCULATE function as a filter. It is your turn to exploit these functions in your amazing dashboard.
You can also watch Youtube video about DATESBETWEEN function with variables in DAX
For more useful blogs, please visit Learn DAX
