Week to Date Calculation in Power BI with DAX

There are a number of pre defined DAX time intelligence calculations that help you to get analytics over time, such as year to date, same period last year, and etc. However, there is no calculation for Week to Date built-in. I have found it quite a demand for some of businesses, as many of businesses work on a weekly periods rather than monthly. So here in this post, I will explain a method to do week to date calculation with DAX. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.

Introduction

There is a set of functions for calculating Year to Date (TotalYTD), Quarter to Date (TotalQTD), and Month to Date (TotalMTD). However, for calculating Week to Date; there is no built-in function. There are many ways to calculate week to date. One of the methods is using functions such as DatesBetween and WeekDay to calculate the period between first day of the week and the date of the filter context. Let’s see how it works.

Sample Dataset

If you want to use this example; create a Power BI file connected to AdventureWorks data source and load FactInternetSales, and DimDate into the model. Create a connection between these two tables based on DateKey (from DimDate), and OrderDateKey (from FactInternetSales).

Mark As Date Table

* for week to date calculation to work; it is not mandatory to have a date table, you can even use the built-in date table of Power BI. all below calculations would work even if you use the built-in date table, you just need to add .[date] at the end of your date field to get it working.

Because in this example; we are going to use DimDate as our date table, select this table in the list of fields (in report tab, or data tab), and mark it as date table.

Select FullDateAlternateKey as the date column.

Create a table visual with CalendarYear, EnglishMonthName, DayNumberOfMonth, and EnglishDayNameOfWeek, and SalesAmount

WeekDay DAX Function

To start the solution; let’s first find out what is the day number of week for any given date. We already have the day number of week in the DimDate provided by AdventureWorks. However, we calculate it again, just in case you use the built-in date table. Using LastDate function we get the date value of the current filter context, and wrap it inside a WeekDay function to fetch the day number of week. here is the DAX statement:

Day Number of Week = WEEKDAY(LASTDATE(DimDate[FullDateAlternateKey]))

The result of this measure would be the day number of week starting from Sunday as 1, ending Saturday as 7.

Starting from Monday

Not always in all businesses the week starts from Sunday. In fact, in many businesses, week starts on Monday. WeekDay function has a second parameter which can determine the starting day of week. parameter name is Return Type.

The default value is 1. means Sunday is 1, and Saturday 7.

If you change it to 2; Monday will be 1, and Sunday 7.

If you change it to 3; Monday will be 0, and Sunday 6. This one will appeal more for the rest of our calculation. Set this parameter to 3.

Day Number of Week = WEEKDAY(LASTDATE(DimDate[FullDateAlternateKey]),3)

 

Start of the Week

Now that we know the day of week, it is easy to calculate start of the week. You need to go that number back as days interval. For example; Wednesday is day 2 of the week. if you go two days back, you get Monday. Using DateAdd function you can go as many days back you want. here is the DAX expression:

Start of Week = 
var CurrentDate=LASTDATE(DimDate[FullDateAlternateKey])
var DayNumberOfWeek=WEEKDAY(LASTDATE(DimDate[FullDateAlternateKey]),3)
return
DATEADD(
    CurrentDate,
    -1*DayNumberOfWeek,
    DAY)

The heart of expression above is DateAdd calculation, we go the number of days back (that is the reason for -1*DayNumberOfWeek). Here is the result. for every give date, we get the Monday (which is start of that week) of that week as the week start date.

Week to Date Calculation

Now that we have the start of week, we can calculate all dates between that date and the date of the current filter context using DatesBetween, and wrap it inside a calculate to calculate Week to Date.

DATESBETWEEN(
    DimDate[FullDateAlternateKey],
DATEADD(
    CurrentDate,
    -1*DayNumberOfWeek,
    DAY),
    CurrentDate)

DAX expression above is using DatesBetween to give us all dates between start of the week (calculated in the previous step), and the date of the current filter context (LastDate(DimDate[FullDateAlternateKey])).

We can then wrap it inside a Calculate function to get the week to date as below;

Week to Date Sales = 
var CurrentDate=LASTDATE(DimDate[FullDateAlternateKey])
var DayNumberOfWeek=WEEKDAY(LASTDATE(DimDate[FullDateAlternateKey]),3)
return
CALCULATE(
    SUM(FactInternetSales[SalesAmount]),
DATESBETWEEN(
    DimDate[FullDateAlternateKey],
DATEADD(
    CurrentDate,
    -1*DayNumberOfWeek,
    DAY),
    CurrentDate))

Here is the result;

Just for a reference, here is the entire Week to Date calculation with DAX again:

Week to Date Sales = 
var CurrentDate=LASTDATE(DimDate[FullDateAlternateKey])
var DayNumberOfWeek=WEEKDAY(LASTDATE(DimDate[FullDateAlternateKey]),3)
return
CALCULATE(
    SUM(FactInternetSales[SalesAmount]),
DATESBETWEEN(
    DimDate[FullDateAlternateKey],
DATEADD(
    CurrentDate,
    -1*DayNumberOfWeek,
    DAY),
    CurrentDate))
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

8 thoughts on “Week to Date Calculation in Power BI with DAX

  • Hi Reza,

    Thank you for this useful solution. I’m trying to do a calc. that shows the previous week but can’t seem to get it to do a “previous week to same day as current week” calc. I can get it to calculate the full week but I want it to calculate only fx. Monday to Thursday if the latest day in the current week is Thursday. Do you have an idea as to how I can tweak the parameters to take this problem in to account?

    I’m thinking that the solution should be something like a datesbetween the first day in previous week shifted by the current weekdaynumber, but I’m having troubles cracking the enddate.

    • Hi Martin.
      Yes, for the Previous WTD, you can use different approaches. One is to go back a week from the current date. which you can do that with DATEADD and -7 days. Then from that date, you can use the approach mentioned here to get the start of that week and use either DatesBetween or DatesInPeriod to calculate the Previous WTD. A blog post on that will be coming.
      Cheers
      Reza

  • Hi, to be able to capture Week to Date Sales has certainly been a challenge, and your information has by far brought me the closest to accomplishing this. However, the formula is not working properly for me. I changed the -1 as it was giving me an incorrect value, and if I change it to -0 it’s just giving me the day of the total sales. Do you have any insight why I’m not getting the WTD? I’m using Power BI and using a Card in the Visualizations to just capture the Week to Date Sales in hopes that it will update once data is refreshed. Thanks!

  • Hi Reza, I actually got the DAX calculation to work! Yay! However, it’s not longer working due to putting the forecast budget for May in the table which extends the calendar days out to the 31st and now I’m getting a 0 value due to the formula looking at the lastdate on the calendar. Is there a way to formulate this to look at today date TODAY() and look back on the starting week date (which is Sunday for us) to capture a week to date sum for sales?

Leave a Reply