Calculate with/without Filter() function and

[Victor_AguirreV]

Hey guys,

So apparently I have been missing something very crucial about how the calculate function works. I am currently on the Datacamp course to get certified, and the question is asking to create a  measure that sums a field, filtering it based on the date of another table.

My initial measure was:

But after trying and failing, the "hint" tells me that the correct formula is:

So I must be missing a good definition on how the calculate formula works. Why is it the correct version of the calculate without the Filter() function? It does give very different results, but I am not sure I understand why?

I was reading something that using it without, it moves from row to filter context... and while I thought I had that well understood... I am not seeing that I really don't. Help please?

[Ashish_Mathur · Accepted solution]

Hi,

It looks like you are trying to calculate a running total by date.  Here's the approach i would use:

1. Create a Calendar Table and a relationship (Many to One and Single) from the Recorded Date column to the Date column of the Calendar Table
2. To your visual, drag Date from the Calendar Table
3. Write this measure

RT = calculate(sum(Expenditures[Budgeted amout]),datesytd(Calendar[Date],"31/12"))

The RT will resent on Jan 1 of each year.

[v-shex-msft]

Hi @Victor_AguirreV,

I'd like to suggest you take a look at the following blog about row and filter context if it help you to learning with Dax knowledges.

Row Context and Filter Context in DAX - SQLBI

Regards,

Xiaoxin Sheng

[Daniel29195]

@Victor_AguirreV 

when you use a filter predicate in calculate,   power bi transform it into this : 

filter (

all ( 'recorded date'[date] ) ,

'recorded date'[date] < = max(expenditures[recorded date])
)

so basically there is a difference between this , and the one you are writing, which is : 

filter( 'recorded date',  'recorded date' <= max ( expenditures(recorded date] ) ) 

and the difference is indicative, since in the first, you are removing any filter from the filter context then add the filter , however in the second one, you are adding a filter to the existing filter context. 

hope this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠