Recently, I have been working on a project involving tabular models. For the uninitiated, tabular models are a recent development in SQL/Excel PowerPivot that combine the flexibility of a conventional database with the data density of a multidimensional cube. One of the asks was to see a week over week view of the data.
No problem, right?
Tabular models utilize DAX, a new language which is very similar to the existing Excel formulas, which so many in BI are familiar with. DAX has numerous time intelligence functions (DATESYTD, SAMEPERIODLASTYEAR, DATESBETWEEN, PREVIOUSMONTH, etc.), so doing week over week should be a snap.
The problem is that it isn’t.
DAX is brilliant for DAY, MONTH, QUARTER, and YEAR, but weekly time intelligence just isn’t an intrinsic part of DAX. You also have to remember that weeks are really tricky to think of in terms of business intelligence. A single week can cross two separate months, or even years.
At that point, I began searching the Internet to find a solution. Luckily, other people had hit this issue, as well. The problem I encountered was that there were multiple possible solutions. The benefit (and drawback) of DAX is that it is incredibly flexible in solving problems, but finding the best solution can be time-consuming.
The solution I ended up using was based on the DAX formulas from the sqlbi blog. However, my data did not have much in common with the data used in the examples provided on the website.
The first problem that I faced was that I did not have any way to differentiate week from week. Tabular models and DAX rely on date tables for their calculations, and the table that I had was based around the calendar date, with months and years split out into their own columns.
However, one of the strengths of the tabular model is that each table in it is based on SQL queries. To bring in a week identifier, I built a SQL query that calculated three things: the Week End Date, the Week Start Date, and a WeekNumberID, which I could use in the DAX expressions.
For the Week End Date, I used the following expression:
Cast (DATEADD (day, ( DATEDIFF (day , 7, [Date])) /7*7 + 7, 6) as date)
– DATEADD modifies the base value ([Date])
– DATEDIFF marks out the number of days difference between the date and the calculated end of the week. This DATEDIFF assumes the end of the week is Sunday (which is what the client used). As an example, if Saturday is the end of the week, the DATEDIFF expression would be DATEDIFF (day , 6, [Date])) /7*7 + 7, 5)
The Week Start Date looks like this: cast (dateadd (day, datediff(day, 0, [Date]) /7*7,0) as date). This expression assumes that Monday is the first day of the week. For Sunday, it would look like this: cast (dateadd (day, datediff(day, -1, [Date]) /7*7,-1) as date).
For the WeekNumberID, I built a subquery that I joined back into the date table using this query:
– Select ROW_NUMBER () Over(Order by [EndOfWeek] ASC) as [WeekNumberID], [EndOfWeek] from (select distinct cast(DATEADD(day, (DATEDIFF(day, 7, [Date])) /7*7 + 7, 6) as date) [EndOfWeek] from dbo.[DimDate]
The ROW_NUMBER () Over(Order) expression brings in the row numbers from the query and orders them by field provided; in this case the [EndOfWeek] field. The [EndOfWeek] field also allowed me to join this query back into the date table, and thus bring in the WeekNumberID.
With that added into the date table, I could start working on the actual DAX formulas for the report. To demonstrate the formulas, I am using the AdventureWorks DW Tabular Model for SQL Server 2014.
So, the base data looks something like this:
To add in a new calculation, you will need to open up the tabular model in Visual Studio:
Navigate to the Internet Sales table. Note that there is a section with a group of formulas already entered. This is where you will add the new formulas. The first formula to add calculates the previous week. This is the final formula I ended up using:
Internet Sales Previous
– Week:=if(HASONEVALUE(‘Date'[WeekNumberID]),CALCULATE([Internet Total Sales],Filter(ALL(‘Date’), ‘Date'[WeekNumberID] = MAX(‘Date'[WeekNumberID])-1 )),BLANK())
The if(HASONEVALUE()) clause works as an error check for the formula. It will only bring in data from the [Internet Total Sales] measure if there is data for the appropriate date range. Otherwise, there will be a blank.
The second problem that I did discovered is that having the [Calendar Year] clause in the formula like the source website had caused issues with the yearly turnover where the measure would be blank for the last week of the year. This is because the week would technically have two values for the year. As a result, it would fail the IF() statement and display a blank value.
The second field to be created is the delta measure between the current week and the previous week, which is quite straightforward:
– Weekly Internet Sales Delta:=if(isblank([Internet Sales Previous Week]),blank(),[Internet Total Sales]-[Internet Sales Previous Week])
The ISBLANK() clause is there so that if there isn’t any previous week (like at the beginning of the data set), no delta will be shown. This cleans up the presentation of the data when you build visualizations.
Before you save your changes, be sure to format these new calculated fields in the Properties window on the lower right. If you use a visualization tool like PowerBI, this is the only place you will be able to format the numbers.
With these new fields in place, the data now looks like this:
Of course, the WeekNumberID is not the best field to use for visualizations, so use the Start of Week field we built before:
Much better! Using these fields, we can also start building visualizations. Here is an area chart for 2011, breaking the week-over-week change down by region:
Now the big question is: “Why use DAX for this? Why not do the calculations in SQL?”
Personally, I prefer using DAX for something like this because it is much faster to change a DAX measure than to rewrite SQL code. Plus, with a tabular model, you will have to take those SQL calculations and turn them into DAX measures regardless. Tabular models use OLAP connections, and will only allow measures to be placed in the Values section of a pivot table.
This should help you get weekly time intelligence into your reporting without too much fuss. DAX is very much a work-in-progress, so as time goes on, hopefully weekly time intelligence will work its way into the DAX toolset.