Week to Date Calculation in Power BI with DAX

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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:

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.

 

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:

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.

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;

Here is the result;

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

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

2 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

Leave a Reply

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