Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I'm accessing a local DB to set up my report. It's an old DB so date format changes have been made during the years.
Some dates are in timestamps eg 1380146400 = 2013-09-25 22:00:00 (no problem customizing a column for that).
But then!
These dates give me a hard time:
2456685 that should be 2014-01-27 12:00:00
2454110 should be 2007-01-09 12:00:00
2459572 should be 2021-12-23 12:00:00
The time is not relevant just the date.
I get the correct value from https://www.onlineconversion.com/julian_date.htm
But how do I get this to work in a custom column i PowerBi?
//BR
Solved! Go to Solution.
So I solved this first by calculating the number of days from -4713/01/01 to 1970/01/01 and subtracting it from the known date to a new column. Then from 1970/01/01 add those days in the new column.
"if [dat_dv] = null then null else [dat_dv] - 2440588" |
"if [DVconv] = null then null else #date(1970,1,1) + #duration([DVconv],0,0,0)" |
Maybe not the most elegant solution, but it works when in Power query.
BR
So I solved this first by calculating the number of days from -4713/01/01 to 1970/01/01 and subtracting it from the known date to a new column. Then from 1970/01/01 add those days in the new column.
"if [dat_dv] = null then null else [dat_dv] - 2440588" |
"if [DVconv] = null then null else #date(1970,1,1) + #duration([DVconv],0,0,0)" |
Maybe not the most elegant solution, but it works when in Power query.
BR
Hi @Frigge__
About the Conversion algorithm, you can refer to this article,
https://docs.kde.org/trunk5/en/kstars/kstars/ai-julianday.html
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
#UPDATE
So I found out that the numbers originate from the Gregorian calendar (?). That means the numbers are days from January 1st 4713 BC.
So how can I add 2459572 days to January 1st 4713 BC and get the correct date in a new column?
@Frigge__ , I see a power Query solution. But your number seems, little bigger compare to the example
https://community.powerbi.com/t5/Desktop/Convert-Julian-Date-to-Calendar-Date/m-p/99936
Hi,
I have tried it, but that won't work.
The attached link is the only "converter" that is working, but I can't figure out how 😫
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
89 | |
84 | |
76 | |
64 |
User | Count |
---|---|
135 | |
112 | |
98 | |
98 | |
92 |