MONTH function error

[Anonymous]

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 solution]

hi @Anonymous 

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 calculated column when you want to evaluate each row
• Use a measure 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 LABEL JAN =
IF (
    MONTH ( MAX ( 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" )
    )
)

Regards,

Lin

[lc_finance]

Hi @Anonymous ,

the reason is that the MONTH function takes a date as input instead of a column of dates.

You can fix that by using LASTDATE that takes as input a column of dates and returns the last date in the column.

The formula can become something like this:

MONTH(LASTDATE(MAXDATE[MaxDate]))

Does this help you?

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com