DAX Measure Query

[SuzieKidd]

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.

[johnt75 · Accepted solution]

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.

[v-sdhruv]

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

[v-sdhruv]

Hi @SuzieKidd ,

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

[v-sdhruv]

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

[v-sdhruv · Accepted solution]

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!

[FBergamaschi]

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

[johnt75 · Accepted solution]

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.

[SuzieKidd]

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

Suzie