Forum Discussion

waleed111's avatar
waleed111
Helper V
5 years ago
Solved

YEAR function

YEAR function generate wrong values: Year A = Year('All Manufacturings (4)'[Added Time]) it gave me 1905 for all values, what is the problem?  
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hello @waleed111 ,

    Based on the sample data, how did you create this [Time Added] column? Basically the Year() function will return the corresponding year of the column value and works well in my environment if the column is using formula or quote directly.

    y1.pngy2.png

    Maybe you can also try using .year to check:

    Column 3 = 'Calendar'[Date]. [Year]

    In addition, if you use the format() function to get the year value, it will be displayed as the default text type and can only be converted to the sunch number type as integer, decimal number, and so on, the date type is not allowed.

    Like this:

    Column 4 = CONVERT(FORMAT('Calendar'[Date],"YYYY"),DOUBLE)
Column 4 = CONVERT(FORMAT('Calendar'[Date],"YYYY"),INTEGER)

    Best Looks,
    Yingjie Li

    If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
5 years ago

waleed111 , Was it the date time column having only time added with some days. or was it the duration column.  Power does add date to the time column. because of that, you might get that.

 

Can you share sample data and sample output in table format?

 

 

  • waleed111's avatar
    waleed111
    Helper V
    5 years ago

     

    • VijayP's avatar
      VijayP
      Community Champion
      5 years ago

      waleed111 

      It must be Data Type issue in the Add Column . Have you checked the DAta type OR

      Try using Format([Date],"YYYY") and let me know whethe you are able to get the Year

      • waleed111's avatar
        waleed111
        Helper V
        5 years ago

        now FORMAT function work but it show me this error:

        Cannot convert value '2020' of type Text to type Date.

    • v-yingjl's avatar
      v-yingjl
      Community Support
      5 years ago

      Hello @waleed111 ,

      Based on the sample data, how did you create this [Time Added] column? Basically the Year() function will return the corresponding year of the column value and works well in my environment if the column is using formula or quote directly.

      y1.pngy2.png

      Maybe you can also try using .year to check:

      Column 3 = 'Calendar'[Date]. [Year]

      In addition, if you use the format() function to get the year value, it will be displayed as the default text type and can only be converted to the sunch number type as integer, decimal number, and so on, the date type is not allowed.

      Like this:

      Column 4 = CONVERT(FORMAT('Calendar'[Date],"YYYY"),DOUBLE)
Column 4 = CONVERT(FORMAT('Calendar'[Date],"YYYY"),INTEGER)

      Best Looks,
      Yingjie Li

      If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.