Calculate Works until i add FILTER

DAX DAX Calculations
1

Hello Guys

I’m learning DAX and taking the online courses here , i was trying to make a measure for Sales Last year and I’ve been encountering this problem

this formula works :

Sales LY =CALCULATE([Sales CY],SAMEPERIODLASTYEAR(Date[Date]))

But when i add FILTER to the measure give me the values from the current year like “Sales CY”

Sales LY = CALCULATE([Sales CY], Filter (Date, SAMEPERIODLASTYEAR (Date[Date]) )

)

  • I already have a date filter on the page relative date in this year
  • the invoices Table and Date table are joined on the date of the creation of the invoice (createat)
  • Data Model

model
model642×790 27.8 KB

Can you explain to me why the first calculation works and the second does not ?
any help will be much appreciated
Kind regards

2

@zodijacky Did you ask this question on the Discord? I saw a similar question today.

SAMEPERIODLASTYEAR is a table function and you are using it in the FilterExpression argument where FILTER only expects scalar/boolean values.

Most of the time intelligence functions initiate context transition when called in the row context, that’s why you will never see anyone writing

FILTER ( Dates, LASTDATE ( Dates[Date] ) )
or
FILTER ( Dates, DATEADD ( Dates[Date] ),1, MONTH ) )

or any other time intelligence function in the row context of an iterator

For each row of the dates inside FILTER, SAMEPERIODLASTYEAR initiates context transition and moves the currently iterated date from the row context to the filter context and then moves the date to the previous year but that doesn’t have any impact on the result of FILTER and FILTER return all the dates visible in the current filter context which are for the current year that’s why you see the sales of the current year.

4 Likes
3

Dear @AntrikshSharma
Thanks for your thorough explanation.

Best

1 Like