Cannot convert value '' of type Text to type Number.

[Anonymous]

Hi

I am trying to create a new column with the following logic, but get the error message:
Cannot convert value '' of type Text to type Number.

This is my logic (mo_date is formatted as a shorthand date dd/mm/yyyy):

FY =

if(month(dim_mo[mo_date]) >=9,

format(dim_mo[mo_date], "YY") & "/" & ((format(dim_mo[mo_date], "YY")*1)+1),

((format(dim_mo[mo_date], "YY")*1)-1) & "/" & format(dim_mo[mo_date],"YY") )

Any idea how to get around this please?

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

Because format([date],"YY'") becomes text format. It is no longer possible to calculate, for example, in 2021, it will become "21" after format, and the format is text format *1-1 and cannot be calculated. So you can change to the following dax.

FY =
IF (
    MONTH ( dim_mo[mo_date] ) >= 9,
    FORMAT ( dim_mo[mo_date], "YY" ) & "/"
        & RIGHT ( YEAR ( dim_mo[mo_date] ) + 1, 2 ),
    RIGHT ( YEAR ( dim_mo[mo_date] ) - 1, 2 ) & "/"
        & FORMAT ( dim_mo[mo_date], "YY" )
)

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

Because format([date],"YY'") becomes text format. It is no longer possible to calculate, for example, in 2021, it will become "21" after format, and the format is text format *1-1 and cannot be calculated. So you can change to the following dax.

FY =
IF (
    MONTH ( dim_mo[mo_date] ) >= 9,
    FORMAT ( dim_mo[mo_date], "YY" ) & "/"
        & RIGHT ( YEAR ( dim_mo[mo_date] ) + 1, 2 ),
    RIGHT ( YEAR ( dim_mo[mo_date] ) - 1, 2 ) & "/"
        & FORMAT ( dim_mo[mo_date], "YY" )
)

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Try this calculated column formula

=if(month(dim_mo[mo_date]) >=9,year(dim_mo[mo_date])&"/"&year(dim_mo[mo_date])+1,year(dim_mo[mo_date])-1&"/"&year(dim_mo[mo_date]))

Hope this helps.

[Anonymous]

Thanks for your suggestion. I have tried this but get the following error message:

The syntax for '+' is incorrect.

[Ashish_Mathur]

Mine is a calculated column formula in DAX not a custom formula in M.  Ensure that you are writing that as a calculated column formula in DAX.  If it still does not help, then share the download link of your PBI file.

[vanessafvg]

is your new column datatype text or a whole number?

what are you expecting?

[Anonymous]

I have looked at the date field and can see I have (blanks) in there. Would that be what is causing this not to work? 

[vanessafvg · Accepted solution]

yes probaby you would need to handle your blanks by using an if statement something like

if(isblank(date), blank(), your conversion)

[Anonymous]

It's a text. The output I am expecting is the same are you're getting so not quite sure why mind doesn't work. Thanks for checking