cancel
Showing results for
Did you mean:
Highlighted
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.

Best regards

Elijah

1 ACCEPTED SOLUTION

Accepted Solutions
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.))
RETURN MAXX(FILTER(__tmpTable,[DayOfYear]=__DayOfYear),[Value])```

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

10 REPLIES 10
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

Proud to be a Datanaut!

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

## 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

Proud to be a Datanaut!

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

## 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.))
RETURN MAXX(FILTER(__tmpTable,[DayOfYear]=__DayOfYear),[Value])```

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

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.))
RETURN MAXX(FILTER(__tmpTable,[DayOfYear]=__DayOfYear),[Value])```

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

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

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 ?

Elijah

Regular Visitor

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

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

Announcements