MONTH function error

Question

I have a MAXDATE table with MaxDate column which contain dates in Text format. Now iam trying to use MONTH function in if condition to get Month value. When iam trying to do this, it is throwing error as follows;

"A single value for column 'MaxDate' in table 'MAXDATE' cannot be determined. This can happen when a measure 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."

For reference attaching the dax measure used:

MTD LABEL JAN = IF(MONTH(MAXDATE1[MaxDate])=01 && CEILING(DAY(MAXDATE/7),1)< CALCULATE(MAX(Query1[MaxNumOfPayWeeksInMonth]),FILTER(Query1, Query1[MONTH] = "01/2019")),

CALCULATE(SUM(Query1[MID_MONTH_NET_AMOUNT]), FILTER(Query1, Query1[MONTH] = "01/2019" && Query1[WeekNum] = "<=2")),

CALCULATE(SUM(Query1[MTD_NET_AMOUNT]), FILTER(Query1, Query1[MONTH] = "01/2019")))

MONTH(MAXDATE(MaxDate)- even this throwing same error

v-lili6-msft · Accepted Answer

hi&nbsp;Anonymous&nbsp;
First, you should know the difference between calculated column and calculated measure.
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
https://www.blue-granite.com/blog/understanding-the-differences-between-calculated-columns-measures-in-power-bi

Use a&nbsp;calculated column&nbsp;when you want to evaluate each row
Use a&nbsp;measure&nbsp;when you need an aggregate

Your formula is used for a calculated column.
If you want to use the same logic to create a measure, please adjust the formula as below:
MTD&nbsp;LABEL&nbsp;JAN&nbsp;=IF&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;MONTH&nbsp;(&nbsp;MAX&nbsp;(&nbsp;MAXDATE1[MaxDate]&nbsp;)&nbsp;)&nbsp;=&nbsp;01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;CEILING&nbsp;(&nbsp;DAY&nbsp;(&nbsp;MAXDATE&nbsp;/&nbsp;7&nbsp;),&nbsp;1&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MAX&nbsp;(&nbsp;Query1[MaxNumOfPayWeeksInMonth]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;Query1,&nbsp;Query1[MONTH]&nbsp;=&nbsp;"01/2019"&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Query1[MID_MONTH_NET_AMOUNT]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;Query1,&nbsp;Query1[MONTH]&nbsp;=&nbsp;"01/2019"&nbsp;&amp;&amp;&nbsp;Query1[WeekNum]&nbsp;=&nbsp;"&lt;=2"&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;Query1[MTD_NET_AMOUNT]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;Query1,&nbsp;Query1[MONTH]&nbsp;=&nbsp;"01/2019"&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;))
&nbsp;
Regards,
Lin

Forum Discussion

MONTH function error

2 Replies

Related content

AI functions with PySpark error

Cummulative YTD Totals By Month Error

Dax Time Intelligence Functions Weird behavior - 13 Month Rolling Calc

An unexpected error occurred (file '', line , function '')

"An unexpected error occurred (file '', line , function '')" error