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 sho...
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)

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)