cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

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

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.

1 ACCEPTED SOLUTION
MVP

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 smallereasier 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 !

2 REPLIES 2
MVP

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 smallereasier 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 !

Helper II

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors