Working with Weeks in Power BI

We often need to deal with data over a period of weeks, so we’re going to show you how to use Power BI to work with Weeks.

Let’s suppose we have a Sales dataset with Date, Category and Revenue columns and we want to see how the sales perform by Week.

 

If we load this to Power BI Desktop, we get a very simple model like in the image below:

 

We can see that Power BI automatically detected the numeric value column (Revenue), and the date value column (Date). Further, Power BI automatically created a Data Hierarchy for us, so we can easily use the Year, Quarter, Month and Day in the Date column.

For example, we can put Month to the axis and see the Revenue by Month for each category:

But what if we want to see the Revenue by Week? We could put the Day in axis and then approximate the revenue over the course of seven days, but that isn’t simple, direct or accurate:

 

Fortunately, DAX offers a function to extract Week number from the given date. This function is WEEKNUM and requires a date parameter. So we provide the Date and function returns the Week number of that date.

 

The second parameter allows us to decide: (1) if we want the week to begin on Sunday or (2) if we want the week to begin on Monday. We choose the second option, so the complete formula to extract Week number from the Date column is:

1. Week Number = WEEKNUM(Sheet1[Date],2)

Now we can see what the column looks like:

Another function that we will use in this example is the WEEKDAY function which returns Day of the week for the given Date parameter. The syntax for this function is:

1. Week Day = WEEKDAY(Sheet1[Date],2)

If we add this column to the existing table visual, we will have:

We can see that for example, January 7^th of 2018 was the 7^th day of the week, which means it was Sunday.

Now we can use these two columns to create nice visualizations, but let us do one final step, let’s add the day name to our table. We can do that by adding another column using the IF function. The syntax looks like this:

1. Day Name = IF(Sheet1[Week Day]=1, "Monday",

 

IF(Sheet1[Week Day]=2, "Tuesday",

IF(Sheet1[Week Day]=3, "Wednesday",

IF(Sheet1[Week Day]=4, "Thursday",

IF(Sheet1[Week Day]=5, "Friday",

IF(Sheet1[Week Day]=6, "Saturday", "Sunday"))))))

 

We notice that the IF function can be used just like in Excel.

We have the Day Name and if we put it in existing table visual, we will have the image to the right.

Now, we are ready to create visualizations and to see how our Revenue by Week is.

By putting Week Number in Axis and Revenue in Value, we will have a nice chart showing Revenue by Week number:

We can also see Revenues by Day Name:

The first thing that we notice here is that the days are not properly sorted. They have sorted alphabetically which in our case is not the best way to sort. To sort the days properly (from Monday to Sunday) we will use the Sort by Column option provided in the Modeling tab in Power BI Desktop.

We select Day Name in the Fields pane, go to the Modelling tab, click Sort by Column and finally select Week Day.

When we click that, the same chart by Day Names now looks like it should:

We can also put the days in a Slicer like this:

Here we can see Revenues by Week Number sliced for the selected value in Slicer (Wednesday).

We can also use Week Number and Day Name as a part of the Hierarchy like in the below image. Like this, we can use Drill Down options to go back and forth in the Year >> Week Number>> Day Name Hierarchy.

And there you have it! Now you have the tools you need to begin using weeks in Power BI to create the visualization you need.

We’re always looking to answer your questions and give you more feedback on using Power BI to it’s greatest advantage. Learn more about Jet Reports and how it works with Power BI to uncover even better ways to access and visualize your valuable data by checking out our Interactive Preview.