cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elijah2018 Regular Visitor
Regular Visitor

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
Super User

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])

I have book! Learn Power BI from Packt


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
Super User

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

elijah2018 Regular Visitor
Regular Visitor

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
Super User

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

elijah2018 Regular Visitor
Regular Visitor

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
Super User

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])

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

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])

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

elijah2018 Regular Visitor
Regular Visitor

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 Regular Visitor
Regular Visitor

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 Regular Visitor
Regular Visitor

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 Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)