Forum Discussion
Calculate with/without Filter() function and
Hi,
It looks like you are trying to calculate a running total by date. Here's the approach i would use:
- 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
- To your visual, drag Date from the Calendar Table
- Write this measure
RT = calculate(sum(Expenditures[Budgeted amout]),datesytd(Calendar[Date],"31/12"))
The RT will resent on Jan 1 of each year.
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! 🤠