How to Slice Data by Week in a Calendar Table using DAX

By: Kenneth A. Omorodion   |   Updated: 2023-04-12   |   Comments (1)   |   Related: > Dates

Problem

Judging from my experience working with business stakeholders across several organizations, slicing data by current week, previous week, two weeks ago, and so on has become a requirement for reporting. But the question is how can you achieve this in DAX with your tables that include dates? I will demonstrate a simple approach to achieve this requirement.

Solution

To demonstrate how to slice data by week, below is the final solution to achieve this and we will walk through how this can be done with your data.

For the image above, after creating my Dates Table, I needed to create a new column on the Dates Table with the "Unified Week Identity" values as seen.

So, if this column is used as a slicer, we should see something like the image below. Note: The values on the slicer are already sorted to ensure "Current Week" is always at the top.

To demonstrate how you can achieve this, here is a step-by-step approach that, if you follow it accordingly, should be very simple.

Step 1: Create a Dates Table (if you do not have one already)

Several other blog posts and YouTube videos demonstrate how to create a Dates Table. I also have written on this topic: Create Calendar Table Using Power Query M Language and How to Create Date Dimension Tables in Power BI from Text Columns. You can also read more about creating a Dates Table in Power BI in Microsoft Documentation.

In this tip, I will use a different but efficient approach to create a Dates Table, as seen in the code below.

DatesTable = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 03, 01 ), DATE ( 2023, 03, 31 ) ), --- Dates for only month of March 2023.
    "Year", YEAR ( [Date] ),
    "Month Num", MONTH ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmm" ),
    "Week Num", WEEKNUM ( [Date], 1 ), --- Week start day is Sunday
    "Week Name", COMBINEVALUES ( " ", "Wk", WEEKNUM ( [Date], 1 ) )
)

You can adjust the Start Date and End Date to your business dates. I have narrowed down the dates in this tip for demonstration purposes. Where possible, ensure that all the columns I created, and the mandatory 'Date' column are included in yours (particularly the "Week Name" column, as we will need it later).

The image below is the output table of the above DAX code.

Step 2: Use DAX to Identify Current Week Dates Dynamically

For this step, we need to create a new column to identify the current week. Note: This tip was written on 17 March 2023; thus, it should fall into Week 11, according to the image above. Also, in this solution, the week starts on Sundays and ends on Saturdays.

To identify the current week, I have used the code below. Please do some more reading on the DAX functions WEEKNUM and TODAY.

 IsCurrWeek = 
    WEEKNUM ( DatesTable[Date], 1 ) = WEEKNUM ( TODAY (), 1 )

The new column IsCurrWeek will appear on the Dates Table, as seen in the image below.

As you can see, where the current date falls into the current week, it has identified them as "True" and vice versa. But let's take this a step further by ensuring that whenever a current week date is identified, the column will show those dates as "Current Week". To do this, we need to create another column using the DAX code below.

IsCurrWk Identity = 
VAR _IsThisWeek =
    WEEKNUM ( DatesTable[Date], 1 ) = WEEKNUM ( TODAY (), 1 )
RETURN
    SWITCH ( TRUE (), _IsThisWeek, "Current Week" )

This new column IsCurrWk Identity will show on the Dates Table, as seen in the image below.

Step 3: Use DAX to Identify Previous Week Dates Dynamically

Similar to the Current Week, we need to create a column to identify the Previous Week. To do this, use the DAX code below.

 IsPrevWeek = 
    WEEKNUM ( DatesTable[Date], 1 ) = WEEKNUM ( TODAY () - 7, 1 )

The image below shows the output of this DAX code on the existing Dates Table.

As we did for the Current Week, let's create an identity column to show "Previous Week" instead of "True". To do this, use the DAX code below.

IsPrevWk Identity = 
VAR _IsPrevWeek =
    WEEKNUM ( DatesTable[Date], 1 ) = WEEKNUM ( TODAY () - 7, 1)
RETURN
    SWITCH ( TRUE (), _IsPrevWeek, "Previous Week" ) 

The new identity column for the "Previous Week" is seen in the image below.

Step 4: Use DAX to Identify Two Weeks Ago Dates Dynamically

We can create the same for Two Weeks Ago using the DAX code below.

IsTwoWeeksAgo = 
    WEEKNUM ( DatesTable[Date], 1 ) = WEEKNUM ( TODAY () - 14, 1 )

The output of the above code is seen in the image below.

As usual, we need to assign a "Two Weeks Ago" identity name to the rows of dates where the value is "True" for two weeks ago using the DAX code below.

IsTwoWeeksAgo Identity = 
VAR _Is2Week =
    WEEKNUM ( DatesTable[Date], 1 ) = WEEKNUM ( TODAY () - 14, 1)
RETURN
    SWITCH ( TRUE (), _Is2Week, "Two Wks Ago" ) 

The output of the above code is seen in the below image.

Step 5: Unify All the Created Identity Columns into a Single Column

As expected, you might ask: Kenneth, why didn't we just do this step from the beginning? Yes, you can do that. But by breaking it down to make it clearer, I ensured that developers could understand the process in detail and be able to proof check if there were any errors. When you look at the DAX code that unifies the columns, the previous steps will be appreciated.

The DAX code below can be used to unify all the identity columns required. Note: Instead of the SWITCH function, you could use the IF function to generate the same output.

Unified Week Identity = 
VAR _IsThisWk =
    WEEKNUM ( DatesTable[Date], 1 ) = WEEKNUM ( TODAY (), 1 )
VAR _IsPrevWk =
    WEEKNUM ( DatesTable[Date], 1 )
        = WEEKNUM ( TODAY () - 7, 1 )
VAR _Is2WkAgo =
    WEEKNUM ( DatesTable[Date], 1 )
        = WEEKNUM ( TODAY () - 14, 1 )
RETURN
    SWITCH (
        TRUE (),
        _IsThisWk, "Current Week",
        _IsPrevWk, "Previous Week",  
        _Is2WkAgo, "Two Weeks Ago",
        DatesTable[Week Name]
    )

The output of the DAX code above is seen in the image below. Note: In place of the Else optional argument in the SWITCH function, I used the Week Name column created earlier. In your case, you could use any other value or column. Using the Week Name, in this case, ensures that when the Unified Week Identity column is used as a slicer, the sorting will be alphabetical, ensuring the Current Week is always at the top.

In the end, if you remove the other columns created earlier, you should have a final Dates Table, as seen in the image below.

In summary, I have successfully demonstrated how to create a week identity column on your usual Dates Table or Calendar Table, depending on what you call it. It should also be noted that apart from using this column for slicer purposes, it is also commonly used in the filter pane to allow filtering and can be used for filtering within other DAX calculations. I am sure some experts use other approaches to create this column, and I would be happy to see your approaches in the comments section. Otherwise, I highly recommend that readers check out the links in the Next Steps below.

Next Steps

• Read more about the Power BI Dates Table in this Microsoft documentation.
• Read more on the WEEKNUM DAX function.
• Read more on the DAX TODAY function.
• Read more on the SWITCH DAX function.

About the author

Kenneth A. Omorodion is a Business Intelligence Developer with over eight years of experience. He holds both a bachelor’s and master’s degree (Middlesex University in London). Kenneth has the MCSA, Microsoft Data Analyst - Power BI and Azure Fundamentals certifications. Kenneth is a Microsoft Certified Trainer and has delivered corporate training on Power BI, SQL Server, Excel and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips