DATESBETWEEN

How to Use the Power BI DAX function DATESBETWEEN

What is the DATESBETWEEN function?

The DATESBETWEEN function is a time intelligence function in Power BI that returns a table of dates that falls between a start and end date. This function takes two arguments: start date and end date. The syntax for the DATESBETWEEN function is:

DATESBETWEEN(❰dates❱, ❰startdate❱, ❰enddate❱)

The first argument is a table of dates, and the second and third arguments are the start and end dates, respectively.

How to use the DATESBETWEEN function?

Let's say you have a sales table, and you want to analyze sales data between January 1, 2021, and June 30, 2021. To do this, you can use the DATESBETWEEN function as follows:

1. Open Power BI Desktop and load your data into it.

2. Create a new measure by clicking on the "New Measure" button on the "Modeling" tab.

3. Enter the following formula in the formula bar:

SalesBetweenDates = CALCULATE(SUM(Sales[SalesAmount]), DATESBETWEEN(‘Calendar'[Date], DATE(2021, 1, 1), DATE(2021, 6, 30)))

In this formula, Sales[SalesAmount] is the column that contains the sales data, 'Calendar'[Date] is the column that contains the date data, and DATE(2021, 1, 1) and DATE(2021, 6, 30) are the start and end dates, respectively.

4. Click on the "Enter" key to create the measure.

5. Now, you can use the SalesBetweenDates measure in your visualizations to analyze sales data between January 1, 2021, and June 30, 2021.

Tips for using the DATESBETWEEN function

Here are some tips for using the DATESBETWEEN function in Power BI:

1. Use a calendar table

The DATESBETWEEN function requires a table of dates to work. It is recommended that you create a separate calendar table that contains a list of all dates in your dataset. This table can be linked to other tables in your data model, and you can use it to create time intelligence calculations.

2. Use date functions

Power BI has a variety of date functions that you can use with the DATESBETWEEN function. These functions allow you to manipulate date data in various ways. For example, you can use the MONTH function to extract the month from a date or the YEAR function to extract the year from a date.

3. Use the filter context

The DATESBETWEEN function works within the filter context of your report. This means that any filters that are applied to your report will affect the results of the DATESBETWEEN function. For example, if you have a filter that limits the date range to a specific year, the DATESBETWEEN function will only return data for that year.

4. Use the ALL function

If you want to ignore any filters that are applied to your report and return data for all dates, you can use the ALL function. The ALL function removes all filters from a column, allowing you to see all data in that column. For example, you can modify the previous formula to ignore any filters on the 'Calendar'[Date] column as follows:

SalesBetweenDates = CALCULATE(SUM(Sales[SalesAmount]), ALL(‘Calendar'[Date]), DATESBETWEEN(‘Calendar'[Date], DATE(2021, 1, 1), DATE(2021, 6, 30)))

The DATESBETWEEN function is a powerful tool for analyzing data within a specific date range. It is easy to use and can be combined with other date functions to perform complex calculations. By following the tips outlined in this article, you can use the DATESBETWEEN function to gain valuable insights into your data.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)