Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

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?
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    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.

  • vanessafvg's avatar
    vanessafvg
    3 years ago

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

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

8 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    3 years ago

    is your new column datatype text or a whole number?

     

    what are you expecting?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        vanessafvg
        Icon for Community Champion rankCommunity Champion
        3 years ago

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

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

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

      The syntax for '+' is incorrect.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        3 years ago

        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.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.