topic Re: DAX Measure Query in DAX Commands and Tips

DAX Measure Query

SuzieKidd — Tue, 08 Jul 2025 11:42:20 GMT

Hello,

Does anyone know why I get an error when I use this in a measure:

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

Error message : A single value for column 'Date' in table 'Calendar_Lookup' cannot be determined. This can happen when a measure or function formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I'm trying to create a measure to calculate the total cost per financial year considering the days.

Thank you.

Re: DAX Measure Query

johnt75 — Tue, 08 Jul 2025 12:09:34 GMT

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.

Re: DAX Measure Query

FBergamaschi — Tue, 08 Jul 2025 12:12:53 GMT

The reason is that you do not have an active row context on the Calendar_Lookup Table, since a measure does not create a row context on a table, unless you explicit ask so. So you can find the code to use in a measure in the preceding pst by another supporter in the forum, but here is the reason that you were asking for

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: DAX Measure Query

v-sdhruv — Wed, 23 Jul 2025 07:13:32 GMT

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!

Re: DAX Measure Query

v-sdhruv — Wed, 16 Jul 2025 12:33:18 GMT

Hi SuzieKidd ,

Just wanted to check if you had the opportunity to review the suggestions provided?
If any of the response has addressed your query, please accept it as a solution so that other members can easily find it.
Thank You

Re: DAX Measure Query

v-sdhruv — Wed, 23 Jul 2025 07:09:21 GMT

Hi @SuzieKidd ,

Just wanted to check if you had the opportunity to review the suggestions provided?
Thank You

Re: DAX Measure Query

v-sdhruv — Wed, 23 Jul 2025 07:12:05 GMT

Hi @SuzieKidd ,

Just wanted to check if you had the opportunity to review the suggestions provided?
If you still face any issues feel free to reach out.
Thank You

Re: DAX Measure Query

SuzieKidd — Mon, 28 Jul 2025 07:26:08 GMT

Thank you for all the responses, I have now resolved this issue.

Suzie