DAX Measure Query

Try

VAR MaxDate = MAX(Calendar_Lookup[Date])
VAR __fyStart = DATE(YEAR(MaxDate), 4, 1)
VAR __fyEnd = DATE(YEAR(MaxDate) + 1, 3, 31)

That will use the latest date from the filter context.

Hi SuzieKidd ,

As correctly pointed by the users, you do not have an active row context on the Calendar_Lookup Table, therefore you get the error.Since you're referencing Calendar_Lookup[Date] directly in a measure without an aggregation, it throws you this error. Measures operate in a filter context, and if that context includes multiple rows, DAX doesn't know which single value to use unless you specify it.

Maybe you want to aggregate first and then use it-

VAR __fyStart = DATE(YEAR(MAX(Calendar_Lookup[Date])), 4, 1)
VAR __fyEnd = DATE(YEAR(MAX(Calendar_Lookup[Date])) + 1, 3, 31)

Hope this helps!