cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gguliani
Advocate II
Advocate II

Theory Question on using DATESMTD with CALCULATE

Hi All,

MS documentation provides this example below to explain DATESMTD function:

CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), DATESMTD(DateTime[DateKey]))

 

My question is that per the syntax of CALCULATE function, DATESMTD seems like a Filter.

But, here it is not filtering anything. It is just changing the context of calculation to be done per month-to-date.

Then, why is DATESMTD function in the example above considered and placed as "FILTER" in CALCULATE formula?

Is FILTER not equivalent to removing items?

2 ACCEPTED SOLUTIONS
Fowmy
Super User IV
Super User IV

@Gguliani 

 

CALCULATE function accepts the following type of parameters after the Expression.

  • Boolean filter expressions
  • Table filter expressions
  • Filter modification functions

In your MTD example it is a Table Filter expression that is applied here. CALCULATE filters the DateTime[DateKey] column with a range of dates from 1st of the month to the current date in the background when you apply DATESMTD function.

Hope this clear your question?

Articles: 
https://dax.guide/calculate/
https://docs.microsoft.com/en-us/dax/calculate-function-dax


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

AlB
Super User III
Super User III

Hi @Gguliani 

It's a valid point. It all comes down to terminology. Here you should read FILTER not as the usual selection of rows operation but as filter context.  The whole point of  CALCULATE is evaluating the expression in a modified filter context.  Each of those "FILTER" arguments described in the documentation is used for modifying the filter context   and can be either a:

A) filter removal operation (such as ALL, ALLEXCEPT, ALLNOBLANKROW)

B) filter restore operation (ALLSELECTED)

C) table expression, returning a list of values for one or more columns or for an entire expanded table

 

Point C) would be the one at play in your DATESMTD example 

Have a look at https://dax.guide/calculate/  for a more comprehensive overview

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

4 REPLIES 4
AlB
Super User III
Super User III

Hi @Gguliani 

It's a valid point. It all comes down to terminology. Here you should read FILTER not as the usual selection of rows operation but as filter context.  The whole point of  CALCULATE is evaluating the expression in a modified filter context.  Each of those "FILTER" arguments described in the documentation is used for modifying the filter context   and can be either a:

A) filter removal operation (such as ALL, ALLEXCEPT, ALLNOBLANKROW)

B) filter restore operation (ALLSELECTED)

C) table expression, returning a list of values for one or more columns or for an entire expanded table

 

Point C) would be the one at play in your DATESMTD example 

Have a look at https://dax.guide/calculate/  for a more comprehensive overview

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

Fowmy
Super User IV
Super User IV

@Gguliani 

 

CALCULATE function accepts the following type of parameters after the Expression.

  • Boolean filter expressions
  • Table filter expressions
  • Filter modification functions

In your MTD example it is a Table Filter expression that is applied here. CALCULATE filters the DateTime[DateKey] column with a range of dates from 1st of the month to the current date in the background when you apply DATESMTD function.

Hope this clear your question?

Articles: 
https://dax.guide/calculate/
https://docs.microsoft.com/en-us/dax/calculate-function-dax


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

View solution in original post

Thanks @Fowmy that explanation helped. Just a small follow-up query:

Does my explanation below make any sense? I mean does it support, counter your explanation or is it incorrect totally?

DATESMTD (DateTable[Date]) which according to CALCULATE function's syntax appears as being a filter is actually just a context. And when we hover over that FILTER part in the formula- PBI's intelligence suggests that [Filter1] "evaluates an expression in a context modified by filters". Which seem to suggest that even though if it doesn't remove (filter) items it still does calculate the expression per the context provided by DATESMTD

@Gguliani 

You are right. One point on removing filters in addition to adding filters is that CALCULATE also accepts filter modifiers.


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors