The DATEADD Function: The Best And Most Versatile Time Intelligence Function in Power BI

2 comments

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 the DATEADD function is the one you’re looking for.

This, by far, is my go-to time intelligence function in DAX at the moment. After going through this post and the video below, it surely will be your favorite too.

using dateadd function in a formula

This detailed blog will go through all the various aspects of how to set up time comparison analysis with this function.

You can download the Power BI (pbix) file below and follow along throughout the video. Just let us know where to send the file.

Click here to download the DATEADD pbix file

Using the DATEADD Function in Power BI

In this blog, we will use the total sales measure as an example in showing how the DATEADD function can be utilized.

You’ll see how you can compare the sales this year with the sales last year.

But since using DATEADD allows you to compare a measure to any time period, you can look at other time periods such as day, month, or quarter.

replacing day month quarter and year when using dateadd function in a formula

Just by making a few tweaks in the formula, like changing the number of months or replacing month with quarter, you can easily have the data you’re looking for.

sales two quarters ago formula using dateadd function

I think you’ll be amazed at how easy it is to actually do this type of analysis using DAX in Power BI.

Other uses of the DATEADD Function

The usefulness of the DATEADD function does not end with comparing total sales at different time periods. You can actually use it for any metric that you want to look into. For instance, it can be used when calculating the following:

  1. Percentage change
  2. Difference between time periods
  3. Cumulative sales

So, try this out and see how the DATEADD function can be useful to you.

Conclusion

In this post, you’ve seen why the DATEADD is simply the best and most versatile time intelligence function in Power BI.

Not only can you use it to compare a metric to any time period but changing time periods is also really easy. DATEADD can also be used in many other types of calculation.

I’m hoping that from grabbing the download and watching through the video below you’ve now got a much better understanding of how great the DATEADD function is.

Some recommended links for you so you can dive into time intelligence techniques inside of Power BI:

Previous Weeks Sales – Advanced Time Intelligence w/ DAX in Power BI
Create Dynamic Forward Forecasts in Power BI with DAX

Sam

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

membership banner 3

***** Related Links *****
Time Intelligence For Non Standard Date Tables In Power BI
Calculate Financial Year To Date (FYTD) Sales In Power BI using DAX
Discover How To Repeatedly Project A Current Trend Forward In Power BI

***** Related Course Modules *****
Time Intelligence Calculations
Mastering DAX Calculations
Solving Analytical Scenarios w/Power & DAX

***** Related Support Forum Posts *****
Data Modelling based on Custom Time Intelligence Format
Time intelligence Help (Previous Month Calculation)
Project Numbers Forward With Time Intelligence Dax Techniques
For more time intelligence support queries to review see here…..

Enterprise DNA Events

2 comments on “The DATEADD Function: The Best And Most Versatile Time Intelligence Function in Power BI”

  1. Fixed it already, but did not understand why my previous one did not work.

    Pending Revenue Current Quarter = CALCULATE(
    [Total Billing],
    Filter(DimDates,[FiscalQTR]=[Current Fiscal Quarter])
    )

    What would I need to add so that this Filter is not change if other quarters are selected at the page level, e..g this Measure always returns the current quarters number?

    1. Yep you’re on the right track. To finish that off to get that calculation regardless of the date selection you need to firstly remove any context that is coming for the date table. By adding ALL() around the DimDates. That removes any date context then you place it back on with the new logic inside the filter function.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.