Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Cannot convert value of type Text to type Number

Hello Everyone,
 
Up until recently I had a custom column that was able to create the following as a date format.
KPI Start Date = 1*("17/"&[Meeting Date]&"/"&[Year])
 
However after the lastest refresh this column now produces an error. This column is required for various other calcualtions down the line which are now all showing error and blocking visuals from working. This column creates a fake date that avoids the begining or end of the month.
Cannot convert value '17//2022' of type Text to type Number.
 
The [Meeting Date] is a column that uses the switch function to convert month names to numbers and set to be a whole number type (e.g., Jan to 1). 
 
I've tried the useral alterations like having a +0 etc but none of them seem to work. Changing column types doesn't work either. Does anyone have any ideas how to solve this? The calcualtion down the line will not function unless this is in date format as it is needed to compare against previous finicaial year dates.
 
Thank you for your help 🙂 
 
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi TheoC and amitchandak 

     

    I did try both of suggested solutions but neither worked for me. However, the suggestion from amitchandak  did give me an idea. I found a similar function called just "Date" which worked. 

     

    Thank you both for you help 🙂

     

    Louise

     

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , If you have month Name , Try date like 

     

    Date =  datevalue("01-"&[Month Name]&"-" & [Year])

     

    If this does not help
    Can you share sample data and sample output in table format?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi TheoC and amitchandak 

       

      I did try both of suggested solutions but neither worked for me. However, the suggestion from amitchandak  did give me an idea. I found a similar function called just "Date" which worked. 

       

      Thank you both for you help 🙂

       

      Louise

       

       

      • TheoC's avatar
        TheoC
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Anonymous glad you sorted it out. All the best.

  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 

     

    Given that [Meeting Date] is column, can I ask why there is no Table name infront of it (e.g. Table[MeetingDate] rather than just [MeetingDate])?  Similarly for [Year]?  If you look at the below, by adding the Table Name, it seems to give exactly what you're after.

     

     

    Hope this helps 🙂 

     

    Theo