Issue with Custom Measure - Cannot convert value " of type Text to type Number.

[Anonymous]

Hello,

I am building a PBI report that includes some data and DAX coding for a current, working report. I keep getting the error "Cannot convert value " of type Text to type Number" when creating a custom measure, although my code/formatting is the exact same as the report I'm using to build.

I have 2 tables, one named Data and one named Month. The Month table looks like this:

where the "Month" column has data type Date and format 'March 2001'

The measure I am trying to create is in the Data table, with dax formula:

T3M Commission Profit $ = 
var startmonth=value(format(edate(SELECTEDVALUE('Month'[Month]),-2),"yyyyMM"))
var endmonth=SELECTEDVALUE('Month'[YYYYMM])
return Calculate(SUM(Data[CommissionProfitDollars]),FILTER(ALL('Data'), 'Data'[YYYYMM]>=startmonth&&'Data'[YYYYMM]<=endmonth),values(Data[CoworkerName]),VALUES(Data[Team]))

The issue I have found is in the statement.

var startmonth=

Does anyone know why I get an error saying the data is type text when it is date?

[Anonymous]

Hi @Anonymous,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous , First If month is not date then edate(SELECTEDVALUE('Month'[Month]),-2) might not work

better you deal with dates

new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = eomonth(_max, -2) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

[Anonymous]

Thanks for the response! Can you clarify what you mean by the term 'Date' i.e. 

allselected(Date),Date[Date] ?

Would that be the month column from the Month table I have? Sorry if this is a dumb response-- super new to PBI

[ebeery]

@Anonymous have you verified that the 'Data'[YYYYMM] column is a numeric data type?

[Anonymous]

Yes it is!