The expression language of Tabular Mode SSAS – DAX – provides several great time intelligence functions.  One of those it does not provide though a composite function – like DATESMTD or DATESQTD – is one to calculate Week-To-Date summations.  To calculate this type of value, a custom expression using the DATESBETWEEN function is needed.

In this post, I'll show you what inputs are needed for the DATESBETWEEN function, how to create a custom start date using DAX, and how to implement this solution in your Tabular Model.  Note that this solution will also work with PowerPivot for Excel, but the examples show will be using SQL Server Data Tools.

First, let's take a look a simple model I have here:

One fact table — Internet Sales – which relates to a single dimension – Dates.

This model has the correct relationships created, as well as the Dates table being marked as the Date Table of the model, and the field Actual Date flagged as the date field.  With these steps complete I can begin building the pieces I need to create my Week-To-Date calculation.

The DATESBETWEEN function requires three parameters;

· A list of dates

· the start date

· the end date.

In this calculation, I want to use the week start date in the second parameter, and the currently selected date in the third parameter.  Let's take a look at how to create the week start date.

Creating the Week Start Date {.MsoNormal}

In order to create the week start date I need to first decide what day my week starts on.  For example purposes, let's pick Tuesday.  To support this, first I need to add a column that contains the DayNameOfWeek value.  Use the following expression in a calculated field:

=FORMAT('Dates'[ActualDate], "dddd")

Note that my Dates table now looks like this:

Next I need to create an expression that will return to me the previous Tuesday for every date in my table.  Again, I need to create a new column.  The expression to do this is a bit more complicated.  It uses the EARLIER function, which recursively scans the table.

=CALCULATE(
MAX(Dates[ActualDate]),
FILTER(ALL(Dates),
Dates[DayNameOfWeek]="Tuesday" &&
Dates[ActualDate] <= EARLIER(Dates[ActualDate])
)
)

This function is using a clever switching of filtering context to return a set of dates less than the current date, and filter that to include only Tuesdays.  Then, with that set handy, the MAX() function is used to return the latest Tuesday.  This equates to the latest Tuesday less than the current date; or in other words, last Tuesday.  My Dates table now looks like this...

Now that I have a start date AND an end date.  I can finally write my Week-To-Date calculation.  To do this switch over to the fact table and create a new calculation.

Creating the Week-To-Date calculated measure {.MsoNormal}

In my InternetSales table, I want to create the following calculated measure:

[Sales WTD]:=CALCULATE(
SUM('Internet Sales'[SalesAmount]),                        
DATESBETWEEN(         
Dates[ActualDate],
LASTDATE(Dates[WeekStartDate]),
LASTDATE(Dates[ActualDate])
)
);

With all of the ground work done in the date table, this expression ends up to be pretty easy!  It's simply calculating the SUM of [SalesAmount] over a filtered set of DATESBETWEEN() our predefined start date and the end date selected on a report.  Note that in order for this to work, the LASTDATE() function will need to be used.  The DATESBETWEEN() function expects a date value, not a column.  With this calculation created, I can browse the model in Excel and see that it is correctly calculating values.

If you would like to see this in action, I've created a version of this in PowerPivot.  You can download it here.