Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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])
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.
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.
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])
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 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
Thank you again Greg for the help.
You have been tremendous.
Best regards
Elijah Ferreira
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |