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

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.