Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have day of the year. I need to populate date from the day. I cant find which date function to use. I converted the data type from number to date, but it returns the previous date. with year as 1900. See sample below. Please help!
Day of the year | Date in Power BI | Actual Date |
281 | 7th October 1900 | 8th October 2018 |
282 | 8th october 1900 | 9th October 2018 |
Thanks in advance.
Solved! Go to Solution.
I haven't really tested this but the DATE function deals with numbers in a way that should help you.
'date calc'[day] is your 281, for example.
Column = DATE(2018,1,'date calc'[day])
Apologies for the delayed reply. This solution worked! Thanks!
Apologies for the delayed reply. This solution worked! Thanks!
Hi @Anonymous,
By my tests, the fomula of HotChilli should solve your problem.
If you solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry
Try HotChilli's solution first.
Microsoft counts date from 1st January 1900. So the 281st day happened to be the day 7th October 1900.
This silly hack may work:
Date = 'Table'[Day of the Year]+43103
I haven't really tested this but the DATE function deals with numbers in a way that should help you.
'date calc'[day] is your 281, for example.
Column = DATE(2018,1,'date calc'[day])
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
99 | |
97 | |
73 | |
72 |