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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

10 REPLIES 10
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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 🙂

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Yes exacly. 

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

 

Thank you for the help 

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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

Oh yeah, sorry I should have made that more clear. I'm pretty sure it could be done as a measure with a little modification to wrap some of the calculations in aggregations. Let me know if you need that. I actually took some of the calculations and made them into Quick Measures here:

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Date-of-Year/m-p/442421

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Leap-Year/m-p/442398


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank you again Greg for the help. 

You have been tremendous. 

 

 Best regards 

Elijah Ferreira

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.