Converting existing simple measure to UDF - cannot determine a single value for column

KaterinaB — Mon, 11 May 2026 10:59:54 GMT

Hi, newbie to UDFs here, so apologize upfront for the simplicity of the question.
I am trying to test how to implement this converting some simple existing measure patterns into UDFs.

We have a test dataset with some "Last Month" and "Second to Last Month" measures, with such structure

LastMonthProfiles =
VAR LastMonthStart = DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1)
RETURN
CALCULATE(
SUM('Interest Profiles'[Total number of profiles]),
'Interest Profiles'[Month] = LastMonthStart
)

SecondToLastMonthProfiles =
VAR SecondtoLastMonthStart = DATE(YEAR(TODAY()), MONTH(TODAY()) - 2, 1)
RETURN
CALCULATE(
SUM('Interest Profiles'[Total number of profiles]),
'Interest Profiles'[Month] = SecondtoLastMonthStart
)

LastMonthLeads =
VAR LastMonthStart = DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1)
RETURN
CALCULATE(
SUM(MQLs[Number of Leads]),
MQLs[Month] = LastMonthStart
)

SecondtoLastMonthLeads =
VAR SecondtoLastMonthStart = DATE(YEAR(TODAY()), MONTH(TODAY()) - 2, 1)
RETURN
CALCULATE(
SUM(MQLs[Number of Leads]),
MQLs[Month] = SecondtoLastMonthStart
)

I want to have UDF defining the expression, the date column and how many months into the past it should look. I first started just to simply have UDF for Last Month:

DEFINE

FUNCTION LastMonthCalc = (ResultExpression : SCALAR EXPR, DateColumn: SCALAR DATETIME VAL) =>

VAR LastMonth = DATE(YEAR(TODAY()), MONTH(TODAY()) -1, 1)

RETURN

CALCULATE(ResultExpression,

DateColumn = LastMonth)

EVALUATE

{LastMonthCalc(SUM('Interest Profiles'[Total number of profiles]), 'Interest Profiles'[Month])}

I am getting an error: A single value for column 'Month' in table 'Interest Profiles' 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 dont get that error in my existing measures

Re: Converting existing simple measure to UDF - cannot determine a single value for column

johnt75 — Mon, 11 May 2026 10:24:44 GMT

The problem is the type declaration of DateColumn. The function is expecting a scalar value, but you actually want to pass in a column, as you will be applying a filter on that column. Try

DEFINE FUNCTION LastMonthCalc = ( ResultExpression : SCALAR EXPR, DateColumn : COLUMNREF ) => VAR LastMonth = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ) RETURN CALCULATE ( ResultExpression, DateColumn = LastMonth ) EVALUATE { LastMonthCalc ( SUM ( 'Interest Profiles'[Total number of profiles] ), 'Interest Profiles'[Month] ) }

Re: Converting existing simple measure to UDF - cannot determine a single value for column

KaterinaB — Mon, 11 May 2026 11:22:38 GMT

Yep, that was the issue, thanks for the quick help

topic Converting existing simple measure to UDF - cannot determine a single value for column in DAX Commands and Tips

Converting existing simple measure to UDF - cannot determine a single value for column

Re: Converting existing simple measure to UDF - cannot determine a single value for column

Re: Converting existing simple measure to UDF - cannot determine a single value for column