Weekly-Based Time Intelligence in Power BI

In one of my last tutorials here, we learned how to apply time-intelligence in DAX to analyze the COVID-19 data that is provided by Johns Hopkins University. The focus so far was to provide correct calculations on a daily level or to smooth them using running averages here.

In today’s quick tutorial, you will learn how to add weekly-based calculations. You can download the pbix file from here to use as the base-line for this tutorial.

We will start by creating a calculated column in your Calendar table for the week number. Click on the Data view (The second icon on the left sidebar). In the Fields pane on the right sidebar, select the Calendar table and click New column under Table tools.

Enter the following expression in the formula bar:

Week Number = WEEKNUM ( 'Calendar'[Date] )

This WEEKNUM function will return the week number (for more options to set the week number go here). For example, for the date of January 22, 2020, the week number will be 4 since that date is in the fourth week in 2020.

Now you can use this column in the X-axis of your visualizations and apply your measures.

When WEEKNUM is not enough

When you use the Week Number in the X-axis as illustrated in the top bar chart below, your audience may find it a bit difficult to translate a week number with the actual date. In this section, you will learn how to translate the week number to the first date of the week and use it as illustrated in the bottom bar chart below.

When Week Number is not enough. How do conver week numbers to dates of the first day of the week

Go to Table tools and click Calculated column. In the formula bar, enter the formula below.

Year = YEAR ( 'Calendar'[Date] )

Note: You may already have a Year column in your calendar table. It can help you if you build your own date hierarchy in Power BI (You would also need to include the Month column). But in this tutorial, we need the Year column to ensure that we calculate the week start date correctly.

Add another calculated column with this formula:

Week Start Date = 
    MIN ( 'Calendar'[Date] ),
    ALLEXCEPT ( 'Calendar', 'Calendar'[Week Number], 'Calendar'[Year] )

If you need to apply weeks’ end dates in your analysis, here is your modified DAX formula (We only changed the MIN to MAX):

Week End Date = 
    MAX ( 'Calendar'[Date] ),
    ALLEXCEPT ( 'Calendar', 'Calendar'[Week Number], 'Calendar'[Year] )

Let’s explain the code fo the calculated column of Week Start Date. In its core, we apply a MIN on the Date column to get the earliest date that we have for all the records with the same Week Number and Year. To get to the necessary calculation you would need to modify the filter context in DAX to support the requirement in bold above.

Without CALCULATE, the formula MIN( ‘Calendar'[Date] ) will return the earliest date in the entire calendar table, because there is no filter context when you run calculated columns.

The formula CALCULATE ( MIN( ‘Calendar'[Date] ) ) will return the same date you have in each row since the row context is converted into a filter context that includes the selected date.

To modify the filter context in CALCULATE and get all the rows with the same week number and year, we apply the ALLEXCEPT function that will return all the raws in the Calendar table except Week Number and Year which will not be filtered. As a result, rows with different Week Number or Year values will not be included in the result. This operation will return to us the affected result. The MIN function will run only on all the rows in the table with the same Week Number and Year and return the first date of the week.

Note: ALLEXCEPT is a less common function in DAX than ALL. As a result, you may already know how to achieve the same Week Start Date by applying ALL as shown in the formula below, but the function below is not efficient as it will iterate over all the days in the Calendar table.

Week Start Date (Don't use it) =
VAR _weekNum = 'Calendar'[Week Number]
VAR _year = 'Calendar'[Year]
    MIN ( 'Calendar'[Date] ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Week Number] = _weekNum &&
        'Calendar'[Year] = _year

Now you have the Week Start Date as a calculated column. It’s time to format it as a date.

Now, when we have the Week Start Date column we can place it in the X-axis of our visuals and analyze our data by weeks. In one of my previous tutorials here on building the COVID-19 dashboard, we used Time Intelligence to calculate the daily new cases. Here is the equivalent measure that calculates the weekly new cases:

Weekly New Cases = 
VAR current_day = [Total Confirmed]
VAR prev_day = 
    [Total Confirmed],
    DATEADD ( 'Calendar'[Date], -7, DAY )
IF ( ISBLANK ( prev_day ), BLANK(), current_day - prev_day )

By applying the Weekly New Cases in conjunction with the Week Start Date column you can now analyze the weekly new cases of confirmed cases of COVID-19 by weeks and use the week dates instead of numbers.

I hope you found this tutorial useful. In my next blog post, we will continue our exploration of PDF import as started here.


  1. Simi Talkar Reply

    Hello Gil,
    I am an avid follower of your blogs and keenly learn the advanced concepts you present.
    I do have a confusion arising from the MIN and MAX for the start and end of the week respectively presented above.
    While the blog states that the partitioning by Week Number and Year means these columns will NOT be filtered as in:
    ALLEXCEPT function that will return all the raws in the Calendar table except Week Number and Year which will not be filtered.
    The documentation (https://dax.guide/allexcept/ ) states that ALLEXCEPT will clear ALL filters but will keep these(that is they will be filtered?) as in:
    When used as a modifier in CALCULATE or CALCULATETABLE, ALLEXCEPT removes the filters from the
    expanded table specified in the first argument,keeping only the filters in the columns specified in the following arguments.

    • Gil Raviv Post authorReply

      Thank you Simi for sharing. When ai read the explanation of ALLEXCEPT I don’t see any contradiction. When you use my measure do you get the correct results? See what happens when you use ALL and you may see the difference between the two. ALLEXCEPT allows us to keep the columns we mention fixed and clean all the filters for the remaining columns. This allows you to get all the days in the week and year.

  2. Magnus Windsor Reply

    this is the solution to a problem that I’ve been wrestling with over many days. thanks!

  3. marc Reply

    Thank you for this article. However, calculations does not seem to work correctly… For all past weeks [Week Start Date] is correct but for current week, I get “04/26/2021” instead of “04/25/2021”. Why it starts on a Monday for the current week and all previous weeks start with Sunday?

  4. Ruthie09 Reply

    How would I get the x-axis to display the dates as 01-07, 01-14, 01-21, etc. and how can I get this to show YoY (one line for 2020 and one for 2021) vs one long line across both years?

  5. Anonymous Reply

    This year, when 2024 begins from Monday, the provided formulas for calculating the first date of the week are not working correctly, so for the 52nd week, with formula
    Week Number = WEEKNUM ( ‘Calendar'[Date], 21 ),
    we have a minimum week start date of 1st January 2023 (instead of Monday, 25th December 2023), as it was the 52nd week last year.

    So it’s better to use another formula for the calculation of the start and end days of the week:
    Week Start Date = ‘Calendar'[Date] – WEEKDAY (‘Calendar'[Date], 2) + 1
    Week End Date = ‘Calendar'[Week Start Date] + 6

Leave a Reply

Your email address will not be published. Required fields are marked *