cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yatinpurohit
Helper I
Helper I

Strange result for YEAR() function

I have a date column in SharePoint list which a query is retrieving. I have created a calculated column -

FY = YEAR(Table1[Start date])
This gives result: 14133
 
Sample date is 31-01-19. I would expect the result to be 2019.
1 ACCEPTED SOLUTION
d_gosbell
Super User II
Super User II

Year() will produce a numeric column. The default summarize by behaviour for a numeric column is to sum it. So 14133 is probably 2019 * the number of rows in your table. You can change this behaviour by clicking on the column then changing the "Default Summarization" property in the modelling ribbon in Power BI.

 

2019-04-DoNotSummarize.png

 

If you change it to "Do not summarize" Power BI will effectively do a "group by" and just show the distinct values for the year.

View solution in original post

1 REPLY 1
d_gosbell
Super User II
Super User II

Year() will produce a numeric column. The default summarize by behaviour for a numeric column is to sum it. So 14133 is probably 2019 * the number of rows in your table. You can change this behaviour by clicking on the column then changing the "Default Summarization" property in the modelling ribbon in Power BI.

 

2019-04-DoNotSummarize.png

 

If you change it to "Do not summarize" Power BI will effectively do a "group by" and just show the distinct values for the year.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors