Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

EnterpriseDNA

Power BI Time Intelligence Functions - Why DATEADD Is The Best

Time intelligence DAX functions in Power BI enables you to manipulate data using periods, such as days, months, quarters, and years. You can then build up your calculations and make time comparisons over those periods or time frames. 

 

There are many time intelligence functions in Power BI. You’ll find that you’ll use some of them quite a lot while others you may rarely use or never use at all. You can check out the different time intelligence functions on the Enterprise DNA Knowledge Base. This is where you can find Power BI tips and techniques as well as helpful information about DAX functions. 

 

In this blog post, I’m going to highlight the time intelligence functions that I usually use and I’m going to show you why DATEADD is the best and the most versatile of them all. I’ll share with you some of my tutorials around time intelligence so you can see how I use them in my calculations and generate great insights. 

 

The first tutorial that I want you to see is an overview of time intelligence functions, particularly the ones that I’m using most of the time. 

 

2021-04-30 (3).png

 

Most of these functions calculate the same thing, but only at different granularities. For example, in the functions DATESMTDDATESQTD, and DATESYTD, they use months, quarters, and years respectively to calculate the same thing over different time frames. 

 

Here’s the full video tutorial. 

 

 

You can see how I featured DATEADD as a versatile function wherein you can use it to manipulate different time frames. With DATEADD, you are not limited to a particular period. If you need a Power BI time intelligence function that you can use to generate time comparisons over a range of different time frames, then DATEADD is the one you’re looking for. 

 

Here’s another tutorial that features the DATEADD function and why it’s the best. 

 

 

As you can see, in this tutorial, I’m doing time comparisons, specifically calculating this year versus last year results. Here I’m showing two techniques using different time intelligence functions. One is with DATEADD and the other is with SAMEPERIODLASTYEAR.  

 

2021-04-30 (5).png
You’ve seen how flexible and practical DATEADD is. Here’s one more tutorial of mine that showcases DATEADD for different time related insights.  

 

 

These are the time intelligence functions that I believe a Power BI user should familiarize with early on when learning Power BI, especially the DATEADD function. I recommend that you explore more on this function and see how easily you can analyze and compare time frames.  

 

2021-04-30 (8).png

 

Key Take Away 

 

Among the time intelligence functions in Power BI, DATEADD is (to me) the best because it allows you to compare a measure to any period. You can look at other time frames, such as day, month, quarter, or year. 

You can also use DATEADD for any metric that you want to look into, including percentage change, difference between periods, cumulative sales, etc. 

 

I also showed you a couple of ways to calculate this year versus last year. You can use the SAMEPERIODLASTYEAR, but I highly recommend the DATEADD function for time comparisons because it simplifies things. It’s the easiest to use time intelligence function. With its versatility, you can create a range of measures and generate meaningful insights. 

 

Good luck with this one. All the best! 

 

Sam 

 

 

***** Related Links ***** 

Power BI DATEADD Function & Time Related Insights 

Common Time Intelligence Patterns Used In Power BI 

Time Comparison For Non Standard Date Tables In Power BI 

 

***** Related Course Modules***** 

Time Intelligence Calculations 

Mastering DAX Calculations 

Solving Analytical Scenarios w/Power & DAX 

 

***** Related Support Forum Posts ***** 

Problem with DATEADD 

Month To Date Comparisons 

Time Intelligence Help ( Previous Month Calculation) 

For more DATEADD time intelligence support queries to review see here…..