DAX - sum of values identified by a period in a calendar table

[Ormesome]

I have a calendar table linked to my web traffic table on the date field.

I would like to sum the pageviews for the most recent week. If the data is stale this may not be the current week.

I calculate the week containing the most recent date with data in it like so:
_current_week_number = CALCULATE(MAX('Calendar'[Week Number]),'All Website Data'[Date])

Can someone explain why this gives me 58 (correct)
_current_week_pageviews = CALCULATE(SUM('All Website Data'[Pageviews]),FILTER('Calendar',[Week Number] = 43))

but this gives me 792 (the sum of all hits, not just this week)?
_current_week_pageviews = CALCULATE(SUM('All Website Data'[Pageviews]),FILTER('Calendar',[Week Number] = 'All Website Data'[_current_week_number]))

How do I correct that DAX formula without using WEEKNUM('All Website Data'[Date])? There are other measures I need to calculate, like fortnight_pageviews.

[Datatouille · Accepted solution]

Hi @Ormesome

Your idea of using the interim measure "_current_week_number "  is good !

But using a measure inside a filter function activates context transition.

In your particular case, in the filter function of this measure:

CALCULATE(SUM('All Website Data'[Pageviews]),FILTER('Calendar',[Week Number] = 'All Website Data'[_current_week_number]))

You are iterating over each date in your 'Calendar' table (row context). Because you are using [_current_week_number] (which is a measure so it has a Calculate statement wrapped into it) as part of the filter condition, this row context is transformed into an equivalent filter context (context transition). Your measure [_current_week_number] always returns the value of the date iterated. As a result, your filter condition is always true and nothing gets filtered.

Here are my suggestions:

Always use interim measures in the 1st argument of Calculate ( [expression] argument) - it makes the syntax clearer and never has any impact on performance/results.

Only use measures inside Filter arguments when you really want context transition to happen.

So here, If you are using Power Pivot in Excel 2016 or Power BI Desktop, you can leverage DAX variables:

[CurrentWeekPageViews] =

VAR CurrentWeekNumber= CALCULATE(MAX('Calendar'[Week Number]),'All Website Data'[Date]) RETURN

CALCULATE( SUM('All Website Data'[Pageviews]) , Calendar[Week Number] = CurrentWeekNumber )

There must be a better way to compute your CurrentweekNumber but I'd need to see your model and what you expect in order to optimise the calculation of the variable.

There are many pros using DAX variables. It is not just about measure readibility but also performance.

The pros I see using variables :
(i) The formula is smaller, easier to read and to maintain. Users can understand better the role of each component.
(ii) It prevents you from the classic error to forget your "All" function in the Table argument of your filter function.

(iii) The performance is a little bit better since the variable is only evaluated once.

(iv) It is also very convenient when you want to filter a column based on the value of a measure but you don't want context transition to apply (as we did here by storing the desired filtered value in a variable).

Hope I've been clear  and it helps you !

[Ormesome]

Thank you. This was exactly the sort of well reasoned and explained solution I was after.