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

Convert floats to dates, example YYYY.xxxxxxxxxxx to a specific time and day in the calendar year

Hi everyone, 

 

So the data I have in one of my columns of a table is supposed to be a date column. It is in the form of YYYY.xxxxxxxxxxx and when I read it into Power BI the data gets automatically recognized as type number. 

So what i want to do is that I want to convert it to type date where YYYY corresponds to the year and xxxxxxxxxxx corresponds to a specific day in the calendar year of YYYY . 

Note that there is 11 decimals after the YYYY. 

 

I have read a couple of post on how to convert numbers to dates but i havent found anyone that have had the same question as me. 

 

Thank you in advance for your help 

 

Best regards 

 

Elijah

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Convert floats to dates, example YYYY.xxxxxxxxxxx to a specific time and day in the calendar yea

Here it is with Leap Year considerations:

 

DayOfYear = 
VAR __Year = INT([Date])
VAR __Div4 = IF(MOD(__Year,4)=0,TRUE(),FALSE())
VAR __Div100 = IF(MOD(__Year,100)=0,TRUE(),FALSE())
VAR __Div400 = IF(MOD(__Year,400)=0,TRUE(),FALSE())
VAR __IsLeapYear = IF(__Div4 && NOT(__Div100),TRUE(),IF(__Div4 && __Div100 && NOT(__Div400),TRUE(),FALSE()))
VAR __DaysInYear = IF(__IsLeapYear,366,365) 
VAR __DayOfYear = INT(__DaysInYear * ([Date] - __Year * 1.))
VAR __tmpTable = ADDCOLUMNS(GENERATESERIES(DATE(__Year,1,1),DATE(__Year,12,31),1),"DayOfYear",DATEDIFF(DATE(__Year,1,1),[Value],DAY)+1)
RETURN MAXX(FILTER(__tmpTable,[DayOfYear]=__DayOfYear),[Value])

---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

10 REPLIES 10
Super User IV
Super User IV

Re: Convert floats to dates, example YYYY.xxxxxxxxxxx to a specific time and day in the calendar yea

Can you provide an example of the xxxxxxx? What does the xxxxxx represent? Number of days since a specific date?

 

Date types in DAX are actually decimal numbers where the integer portion is the number of days since December 30th, 1899 and the decimal portion is the time.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

elijah2018 Helper I
Helper I

Re: Convert floats to dates, example YYYY.xxxxxxxxxxx to a specific time and day in the calendar yea

Hi Greg . 


Yeah so for example 2031.50238764436, then .50238764436 is supposed to be the fraction of the year. 

So .99999 will then correspond to last december. 

 

Thank you for your intereset in helping me 🙂

Super User IV
Super User IV

Re: Convert floats to dates, example YYYY.xxxxxxxxxxx to a specific time and day in the calendar yea

OK, so with the example you have given, .50238764436, would that then correspond to the 183 day of the year? (365 * .50238764436) with the decimal portion being the time component. I get 183.3714901914 if I do that calculation.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

elijah2018 Helper I
Helper I

Re: Convert floats to dates, example YYYY.xxxxxxxxxxx to a specific time and day in the calendar yea

Yes exacly. 

So how can I convert 183 to a specific day and month with Power BI ? 

 

Thank you for the help 

Super User IV
Super User IV

Re: Convert floats to dates, example YYYY.xxxxxxxxxxx to a specific time and day in the calendar yea

OK, this will get you back a real date from which you can get the Month and Day easily. Probably need to put in a check for leap years though.

 

DayOfYear = 
VAR __Year = INT([Date])
VAR __DayOfYear = INT(365 * ([Date] - __Year * 1.))
VAR __tmpTable = ADDCOLUMNS(GENERATESERIES(DATE(__Year,1,1),DATE(__Year,12,31),1),"DayOfYear",DATEDIFF(DATE(__Year,1,1),[Value],DAY)+1)
RETURN MAXX(FILTER(__tmpTable,[DayOfYear]=__DayOfYear),[Value])

---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV
Super User IV

Re: Convert floats to dates, example YYYY.xxxxxxxxxxx to a specific time and day in the calendar yea

Here it is with Leap Year considerations:

 

DayOfYear = 
VAR __Year = INT([Date])
VAR __Div4 = IF(MOD(__Year,4)=0,TRUE(),FALSE())
VAR __Div100 = IF(MOD(__Year,100)=0,TRUE(),FALSE())
VAR __Div400 = IF(MOD(__Year,400)=0,TRUE(),FALSE())
VAR __IsLeapYear = IF(__Div4 && NOT(__Div100),TRUE(),IF(__Div4 && __Div100 && NOT(__Div400),TRUE(),FALSE()))
VAR __DaysInYear = IF(__IsLeapYear,366,365) 
VAR __DayOfYear = INT(__DaysInYear * ([Date] - __Year * 1.))
VAR __tmpTable = ADDCOLUMNS(GENERATESERIES(DATE(__Year,1,1),DATE(__Year,12,31),1),"DayOfYear",DATEDIFF(DATE(__Year,1,1),[Value],DAY)+1)
RETURN MAXX(FILTER(__tmpTable,[DayOfYear]=__DayOfYear),[Value])

---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

elijah2018 Helper I
Helper I

Re: Convert floats to dates, example YYYY.xxxxxxxxxxx to a specific time and day in the calendar yea

Thank you again Greg for the help. 

You have been tremendous. 

 

 Best regards 

Elijah Ferreira

elijah2018 Helper I
Helper I

Re: Convert floats to dates, example YYYY.xxxxxxxxxxx to a specific time and day in the calendar yea

I just have one more quick  question for you @Greg_Deckler (I am very new to Power BI and Power Query in general) 

 

 

 

So I copyied your code and tried to add as a function that I would then apply on each row in the target column , but it didnt work and I guessed that because its not written as a function . 

So then I tried to add it as a new measure, but Power BI started to complain about not being able to identify the value of the column ''Date'', even though I have a column named date in that table. 

 

What am I doing wrong ? How should your code be implemented ? 

 

Thanks in advance 


Elijah

elijah2018 Helper I
Helper I

Re: Convert floats to dates, example YYYY.xxxxxxxxxxx to a specific time and day in the calendar yea

Hi @Greg_Deckler

 

I got it to work, I realized your code was supposed to be implemented as a calculated column .

 

Thank you so much for the help . 

 

 

Best regards

Elijah

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors