It’s time to start doing some time intelligence analysis. One of best time intelligence functions is DATEADD which enable us to compare different time frames. I use the same data model and continue right after I created the slicers and KPI Cards.
1 Create a table for time comparison
Before writing the formula for Last Year Sales I create a table and drag in Dates from the Dates table and Total Sales from the Main Measures table. When dragging the Date column, Power BI automatically creates a dates hierarchy.

However, I want to see each individual day. To do so, I go under Values and click on the down arrow and there I click on Date.

I also drag the Total Sales measure into the table, and I get the following end result:

2 Calculating Total Sales LY (Last Year) with the DATEADD function
Now it’s time for our Total Sales LY formula. I go ahead and click on the New measure button and write the following formula:
Sales LY =CALCULATE ( [Total Sales], DATEADD ( Dates[Date], -1, YEAR ) )
I start typing CALCULATE. As I type, I see that IntelliSense gives a concise description of what each function does and what format it should have. For CALCULATE it says that it “Evaluates an expression in a context modified by filters“.
In other words, CALCULATE changes the context of the calculation. In terms of its format, it needs to have an expression followed by Filters like so (Expression, [Filter 1] …)
The next step is to insert the Expression and specify what measure to calculate. In this case, Total Sales.
Afterwards I need to specify the Filter. Here is where I put in the DATEADD function because it enables us to filter the period we want to analyze. According to IntelliSense, the DATEADD function “Moves the given set of dates by a specified interval”. Its format is (Dates, Number of Intervals, Interval).
Next, I need to add the Dates and I choose the Date column from the Dates table. Then I can specify the Number of Intervals. In this case I want to see last year so I put in – 1 (minus one). Then I can choose the interval: Day, Month, Quarter or Year. In this case, Year
I type in one bracket to close the DATEADD argument and another one to close the CALCULATE statement.
Last step is to Format to Currency with 0 decimal places and drag in the formula into the table.

Let’s quickly test if the formula returns the correct result. The first transaction date in this data set is 1 January 2015 for 25.345 kr. So on the 1st January 2016 I should have this amount in the Sales LY column and the amount for 1 January 2016. The results in the table confirm that the formula is correct.

DATEADD is a very powerful and versatile time intelligence function because just by changing the interval from Year to Quarter I can calculate Total Sales Last Quarter or if I change the interval to Month then I can calculate Total Sales Last Year. By changing the interval then I can calculate Total Sales 2 Months Ago or Total Sales 2 Quarters Ago and so on.
Moreover, by using the exact formula I can swap the measure from Total Sales to Total Profits for example and get additional insights. To sum up, CALCULATE and DATEADD are two essential functions that can be used in many different scenarios providing excellent time intelligence insights.
Leave a Review