# Power BI DATEADD Function & Time Related Insights

In this tutorial, Iâ€™m going to cover the best time intelligence function, the Power BI DATEADD. Itâ€™s one of the time intelligence functions that you should introduce yourself to as soon as possible when learning Power BI. You may watch the full video of this tutorial at the bottom of this blog.

The Power BI DATEADD DAX function is known for its versatility and ability to analyze and compare different timeframes so easily. Once you understand how to use it, youâ€™ll see how valuable this function is.

Time intelligence is a subset of functions within the DAX formula language that you can utilize to run time-specific analyses. And I’m going to show you how you can do a lot of those analyses with just this one function.

Table of Contents

## Using Power BI DATEADD To Analyze Data From The Previous Year

I have here a really simple calculation of my Total Sales to begin with, but you can use any core measure here.

Itâ€™s important to create your core measures at the beginning of your model development. Core measures include sales, quantity, costs, transactions, and so on. And then from there, you can do all this fancy analysis from those core measures.

From here, I want to analyze my sales the previous year, and I have my Sales LY formula for that. This makes it easy for us to compare one period versus another.

In the table, you can see that the previous year’s sales started only on the first of the sixth in 2014. And so this is the very first number or the amount that is coming through in the sales last year.

Let’s look at the formula.

Iâ€™m using the CALCULATE function for this calculation. With any time intelligence, you should really have a solid understanding of how this function works.

Remember that CALCULATE changes the context of the calculation. And so in this formula, I’ve embedded the Total Sales measure inside CALCULATE. It’s running Total Sales, but I’m changing the context of the calculation.

Then I change the period with the DATEADD function and within DATEADD, I place the Date column.

And then, I do minus one (1) YEAR. I can actually change this into minus two or minus three, etc. And I can also change the timeframe. Instead of YEAR, I can choose DAY, QUARTER, or MONTH. This is where versatility comes in. but in this case, I choose YEAR.

## Power BI DATEADD vs SAMEPERIODLASTYEAR

Now, how about SAMEPERIODLASTYEAR? What’s the difference between the Power BI DATEADD and the SAMEPERIODLASTYEAR function?

The thing about DATEADD is that you can go day, month, quarter, and year. You have all of this versatility. That’s why I believe that DATEADD is just a better way to do it. It’s just super easy to make a time-related analysis with this function.

The SAMEPERIODLASTYEAR function does only one calculation, while DATEADD can do a number of calculations over any number of timeframes.

To demonstrate that, I have here a calculation for sales last quarter (Sales LQ). If you look at the formula, it’s basically the same. All I’ve done is changed the timeframe from year to quarter, and then I’ve got sales last quarter.

And so, we can compare quarter on quarter numbers based on the DATEADD function.

I also have two quarters ago. I just changed the number to -2.

The formula pattern is the same. I just changed the parameters. The same goes to my last three quarters calculation.

Then, you can easily create another visualization out of those measures. Below is an example of how you can do your visualizations. This chart is quite busy though, but you can always take out some information here to make it more meaningful.

## Measure Branching With DATEADD Measures

Another great thing about DATEADD is that itâ€™s a great way to build calculations and enables you to branch out into other calculations. My difference in sales (Diff in Sales) calculation is a good example of measuring branching. I was able to create this formula because I have the Sales LQ.

I simply started with my core measures, and then branched out to this measure. I drag the measure into the table, and now we can see the difference in sales.

I can also create another visualization with my Diff in Sales measure for a more informative report. I can also select on the slicer the quarter that I want to see and analyze.

## Using The Analyst Hub To Format Measures

Now Iâ€™m going to show you how you can create a better formula setup within the Enterprise DNA Analyst Hub.

Let’s take the formula, Sales LY as an example. I just copy and paste it into the DAX Clean Up inside the Analyst Hub.

Here I can format it well so it’s easy to read and understand. Then, I copy the formatted formula by clicking on Copy Code down below.

I’ll paste it back into my model, and now you see that I have nice formatting.

And then, I’m going to go back to the Analyst Hub and save this formula. Iâ€™ll call this DATEADD Last Year, and type in the description. Iâ€™ll click on the share to community button so it will be available to all Analyst Hub users.

With this formula saved in the documents, I can now reference it.

For example, I want to create a new measure for my sales two years ago, I can just quickly copy the formula that I saved in the Analyst Hub, and paste it in here. All I need to do is change the name of the formula (Sales 2yrs Ago) and the parameters (-2). And that is the huge benefit of using the Analyst Hub.

## Conclusion

I hope you can see how important the Power BI DATEADD function is for your time-related analyses. It’s the easiest to use time intelligence function. With its versatility, you can create a range of measures and generate meaningful insights.

I recommend that you explore more on this function and implement it in your own work environment.

All the best,

Sam

[youtube https://www.youtube.com/watch?v=1P99rg9a6es?rel=0&w=784&h=441]

## Create A Power BI Sparkline Chart In Report Builder

In this Power BI Report Builder tutorial, youâ€™ll learn how to add a sparkline chart in your paginated...

## Using Iterating Functions SUMX And AVERAGEX In Power BI

One of the most crucial topics for any Power BI beginner to know about is iterating functions. You may...

## PowerApps Documentation: Using MS Docs For Expert Functionalities

Let's talk about Microsoft's PowerApps documentation and what an important resource it can be for users...

## Data Modeling In Power BI: Tips & Best Practices

In this tutorial, you'll learn valuable tips and best practices for data modeling in Power BI. You can...

## Using The Query Builder Feature In DAX Studio

In this tutorial, youâ€™ll learn how to use the Query Builder feature to easily create queries and...

## Time-Related Insights From Your Supply Chain Metric

For this tutorial, I'm going to cover some high-quality time-related insights directly from your supply...

## Create A Multilingual Power BI Report

For today's blog, I want to discuss a not-unusual situation with many of my consulting assignments for...

## CROSSJOIN DAX Function: Server Timings & Query Plan

In this tutorial, youâ€™ll learn how the CROSSJOIN function works in DAX using the Server Timings pane...

## Overview Of The DAX Studio Keyword COLUMN

Another important keyword to learn when using DAX Studio is the COLUMN keyword. In simplest terms, the...

## Python Cheat Sheet: Essential Quick and Easy Guide

When programming in Python, having a reliable cheat sheet by your side can be a game-changer. Python...

## The Importance Of Creating Compelling Power BI Visualizations

I think this is a good opportunity to run through why creating great visualizations is so important in...

## Scatter Chart Visualizations With Charticulator

In this tutorial, you'll learn how to create a scatter chart for your Power BI report. This is a chart...