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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
VincentCWork
Frequent Visitor

STARTOFYEAR not giving me start of year

Hi,

I'm fairly new to Power Bi and I came upon this problem. I wanted to extract the year from a date in order to properly use is as categorical data on a graph.


I looked at a couple of solution online and one mentionned using the STAROFYEAR function. From what I understand, it should return 01-Jan-"year". For some reason I'm getting all kind of dates returned. 20-Sep-2006 returns 13-Feb-2006 and 01-Oct-2009 returns 06-Jan-2009.


Years for all of them is correct, and months are exclusively January and February. (There are about 20,000 items in the list). I also noticed that all dates from the same year have the same Day-Month value. I thought it might be a date format thing, but as far as January 1st is concerned, dd/mm/yyyy or mm/dd/yyy is the same.

 

VincentCWork_0-1684181505341.png

Any help on this would be appreciated.

1 ACCEPTED SOLUTION
SivaMani
Resident Rockstar
Resident Rockstar

@VincentCWork, Try the below calculation,

 

= DATE(YEAR(<DateColumn>),1,1)

 

STARTOFYEAR DAX Function has a few requirements, Please check this link for more details 

https://dax.guide/startofyear/

View solution in original post

3 REPLIES 3
SivaMani
Resident Rockstar
Resident Rockstar

@VincentCWork, Try the below calculation,

 

= DATE(YEAR(<DateColumn>),1,1)

 

STARTOFYEAR DAX Function has a few requirements, Please check this link for more details 

https://dax.guide/startofyear/

This worked like a charm to get the 1st day of the year.
As this is a solution to the help I requested I accepted it as a solution.

 

Saddly, it turns out that this is not the reason why my data didn't show up correctly when switching it up to "Categorical" type. If you have any tips about that one, please consult my other thread here:

https://community.powerbi.com/t5/Desktop/Display-all-dates-on-a-staked-column-chart/m-p/3236435 

Oh, so since I haven't got an entry for EVERY day of the year, it would give me the FIRST day that I do have for that year? Explaining why all dates are at the start of the year and the same for those in the same year.

Will try your solution first thing tomorrow, but it's nice to know why it didn't work.

Thanks, will return to mark this as a solution if it pans out.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors