Hello there, data enthusiasts! Today, we’re diving into one of Power BI’s nifty DAX functions: DATESBETWEEN. It’s brought to you by Sam McKay, CEO and founder of Enterprise DNA. You can download an audio version if you prefer to listen on the go.
What is DATESBETWEEN?
Simply put, DATESBETWEEN gives you a table with a column of dates. It starts with your chosen start date and goes right through to your end date. It’s like a calendar tailored to your needs.
The DATESBETWEEN Formula
Here’s what the DATESBETWEEN formula looks like:
DATESBETWEEN(
<dates>,
<start_date>,
<end_date>
)Pretty straightforward, right?
Using DATESBETWEEN
So when would you use this function? DATESBETWEEN is your go-to when working with custom date ranges. It’s your bespoke tool for date analysis.
Considerations for DATESBETWEEN
A few things to keep in mind when using DATESBETWEEN:
- If your start date is blank, the function will default to the earliest date in your dates column.
- Likewise, if your end date is blank, it’ll default to the most recent date in the column.
- The start and end dates are inclusive. This means if you made a sale on September 1 and you’ve set that as your start date, that sale is included in your data.
- If you’re dealing with typical date intervals like months, quarters, or years, it might be better to use a function like DATESINPERIOD.
Examples of DATESBETWEEN in Action
Here’s what DATESBETWEEN might look like in your formulas:
= CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), DATESBETWEEN(DateTime[DateKey], DATE(2007,6,1), DATE(2007,8,31)))
= CALCULATE(SUM(FactSales[Net_Sales]), DATESBETWEEN(tbl_Calendar[Date_Id], DATE(2016,10,1), DATE(2016,12,31)))
= CALCULATE(SUM(Sales[Sales Amount]), DATESBETWEEN(Sales[Date], DATE(2015,1,1), DATE(2015,3,31)))That’s it for today’s guide to DATESBETWEEN in Power BI. Remember, Enterprise DNA is here to help you master data skills and AI. Check out their platform for more learning resources. Happy data analyzing!
