Forum Discussion

yatinpurohit's avatar
yatinpurohit
Helper I
7 years ago
Solved

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.
Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    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.

     

     

    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.

1 Reply

  • d_gosbell's avatar
    d_gosbell
    Super User
    7 years ago

    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.

     

     

    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.