Forum Discussion
Issue with Custom Measure - Cannot convert value " of type Text to type Number.
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?
5 Replies
- amitchandak
Super User
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)) 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
