By: Aveek Das | Comments (2) | Related: > Power BI
Problem
Time Intelligence has always been an interesting topic while implementing any Microsoft Business Intelligence (BI) project. May it be from a big enterprise scale to a small Power BI project. Analyzing data over a period helps us understand how the rate of a specific subject has changed in presence of other factors. For example, Year over Year (YoY) or Month to Date (MTD), all these calculations are easily available in DAX for year, month and quarters. However, when it comes to weeks, there is no out of the box formula to compare week values directly.
In this article, I will explain how to generate a value for relative week with respect to the current week in SQL and Power BI. This article assumes that you have some fair knowledge in developing Power BI data models and manipulating data using DAX.
Figure 1 - Relative Week Example
In the figure above, the current week as of writing this article is 8. Thus, the RelativeWeekNumber is 0 for the current week. Similarly, the value is -1 for the previous week and 1 for the next week.
In this example, if you want to report on the last two weeks from today, then
you can just use a filter - "WHERE RelativeWeekNumber <
0 AND RelativeWeekNumber >=-2".
Solution
In this tip, we will explore how to generate the Relative Week Number using both SQL and DAX.
Calculate Relative Week Using SQL
Manipulating the SQL source is much easier as compared to performing any calculations in DAX or any other scripts. For the sake of this article, I'm using a Date Dimension table and the dates between 01-Feb-2020 and 29-Feb-2020. Below is just a simple select statement on the Date dimension table. I have selected only two columns from the table just to keep it simple.
- Date – Returns the full date from the table
- WeekDayName – Returns the name of the weekday of the date.
- ISOWeekOfYear – Returns the ISO week number of the date. Week starts on Monday.
Figure 2 - Selecting data from DimDate
Once we have this data ready, we can go ahead and get the difference between the week number of a Date and the Current Date. This can be done by subtracting the week number of the Date from the Current Date.
For example, WEEKNUMBER('2020-02-19') –
WEEKNUMBER(Current Date) = 0
The difference between both the entities is 0 because the week number is same for both the dates.
Let's see this in action using SQL.
Figure 3 - Adding Relative Week Number
In the above figure, you can see that the RelativeWeekNumber has been calculated and the current week has a value of 0 while the previous and next week have -1 and 1 respectively.
Also, another important point to note is that in this calculation, the RelativeWeekNumber returns a new week starting from Sunday; which is not the ISO standard for the start of a week. If you need to return the week numbers starting from Monday, you can modify the script as below.
Figure 4 - Adding Relative ISO Week Number
SELECT [Date] ,[WeekDayName] ,[ISOWeekOfYear] ,DATEDIFF(WW,GETDATE(),[Date])AS RelativeWeekNumber ,DATEDIFF(WW,GETDATE(),DATEADD(DD,-1,[Date]))AS RelativeISOWeekNumber FROM [dbo].[DimDate] WHERE [Date]BETWEEN'2020-02-01'AND'2020-02-29'
In the Figure 4, if you consider the column – RelativeISOWeekNumber, you can notice the difference with the previous calculated column, i.e. RelativeWeekNumber. For the earlier, the week starts on Monday, while the week starts on Sunday for the latter.
Calculate Relative Week using DAX in Power BI
Now that we have seen how to calculate relative week numbers in SQL, let's also explore how to achieve the same in Power BI using DAX.
For the purpose of this tutorial, I'll create a Power BI report and connect it to the Date Dimension table and fetch the same columns from the source again.
Figure 5 - Importing Data into Power BI
After the data is imported, we can visualize the table that is created in Power BI.
Figure 6 - Visualizing the data table
Now that we have the data ready in Power BI, the next step is to go ahead and create the calculated columns that will store the StartOfWeek and StartOfCurrentWeek values. The DAX script is provided below.
StartOfWeek = DimDate[Date] - WEEKDAY(DimDate[Date],2) + 1 StartOfCurrentWeek = TODAY() - WEEKDAY(TODAY(),2) + 1
Figure 7 - Adding Calculated Columns
The final step is to calculate the relative week value from the two calculated columns that we've just created. This can be done by subtracting the StartOfCurrentWeek from the StartOfWeek value and then dividing the number of days by 7. The DAX script to calculate the RelativeWeek is given below.
RelativeWeek = (DimDate[StartOfWeek] - DimDate[StartOfCurrentWeek])/7
Figure 8 - Calculating Relative Week in DAX
In this article, we have seen how to calculate relative week values in SQL and Power BI using DAX.
Next Steps
- Read more about the Date and Time Functions in DAX.
- Read more on Getting Started with DAX queries.
Learn more about Power BI in this 3 hour training course.
About the author
Aveek Das is an experienced Business Intelligence and Data Analytics professional with over four years of expertise in the Microsoft SQL Server BI stack along with Power BI, Qlik and Tableau.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
