Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
waleed111
Helper V
Helper V

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?
 
1 ACCEPTED SOLUTION

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.

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@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?

 

 

year.PNG

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.

VijayP
Community Champion
Community Champion

@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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


now FORMAT function work but it show me this error:

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

VijayP
Community Champion
Community Champion

@waleed111 

Year can be Text . Any  Specific purpose you want that to be as number.

For Example if you want to use Year in Filter Context , then you can use "2020" to achieve the target

Hope this clarifies. If you have find this as solution please mark this a ssolution and share your Kudoes

Vijay Perepa




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


VijayP
Community Champion
Community Champion

@waleed111 

Year Function only can handle Dates, you are giving Hours:Minutes as an argument




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.