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).
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.
We import this dataset into Power BI Desktop and we will have 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 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.
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.
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.
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.
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:
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 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.
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):
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.
We select Day Name in the Fields Pane, go to the Column Tools, 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 place 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 this.
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.
We will get Revenue by Day for week number 10 of the year 2018 like this.