fbpx Skip to content

Working with Weeks in Power BI

insightsoftware -
April 5, 2023

insightsoftware is a global provider of reporting, analytics, and performance management solutions, empowering organizations to unlock business data and transform the way finance and data teams operate.

Power BI is Not Enough for Reporting

Book a Demo Now

Microsoft’s Power BI strives to support a “data-driven culture” for businesses, allowing users at all levels access to data so they can uncover key insights. However, different businesses have differing requirements for what they want to achieve through working with Power BI, and that’s where things can get tricky. In this article, we discuss a key requirement for many businesses – measuring performance using a date hierarchy (in this case, weeks).

Extend your Power BI reporting by using custom visuals

Power BI Date Hierarchy

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

Weeks in Power BI - Sales Weekly Dataset

We import this dataset into Power BI Desktop and we will have a very simple model like in the image below.

Weeks Power BI Model

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

For example, we can place Month on the X-axis and see the Revenue by Month for each category as it is presented in the next picture.

Weeks Power BI Month

But what if we want to see the Revenue by Week? We could place the Day on the X-axis and then guess approximately how much Revenues in groups of seven days is, but this, of course, is not easy at all.Weeks Power Bi Revenue By Week

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

Weeks Power Bi Dax

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

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

Now we can see how this column looks like.

Weeknumcolumn

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

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

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

Weeks Power BI Weekday

We can see that for example, January 10th of 2018 was the 4th day of the week, which means it was Friday.

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

Day Name = FORMAT(Sheet1[Week Day], "dddd")

With this formula, we are converting the day number into a full-day name.

Now we got the Day Name and if we put it in an existing table visual, we will have.

Weeks Power Bi Day Name

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

By putting Week Number on X-axis and Revenue in Value, we will have a nice chart showing Revenue by Week number (at x-axis formatting pane, choose Type: Categorical and then sort by Week number Ascending):

Power Bi Revenue By Weeks

We can see Revenues by Day Name as well.

The first thing that we notice here is that the days are not properly sorted. They are 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 Sort by Column option provided in Modeling tab in Power BI Desktop.

Weeks Power Bi Revenue Sort

We select Day Name in the Fields Pane, go to the Column Tools, click Sort by Column and finally select Week–day.

Weeks Power Bi Column Tools

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

Weeks Power Bi Revenue Final

We can also place the days in a Slicer like this.

Power Bi Revenues By Week Slicer

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 this.

Weeks Power Bi Hierarchy

Then we can use Drill Down options to back and forth in the Week Number >> Day Name Hierarchy. For example, we set Drill Mode On and then click Week 10.

Power Bi Week Number

We will get Revenue by Day for week number 10 of the year 2018 like this.

Power Bi Week Number Results

10 2021 Whitepaper Jetanalytics Fiveunknownfactsaboutpowerbi Website

Five Unknown Facts About Power BI That Create Project Risk

Download Now: