Week to Date Calculation in Power BI with DAX

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

Leave a Reply

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