𝐏𝐨𝐰𝐞𝐫 𝐁𝐈 𝐚𝐧𝐝 𝐄𝐱𝐜𝐞𝐥 - 𝐃𝐚𝐭𝐞 𝐓𝐢𝐩 - 23 Feb 2022
𝐅𝐢𝐧𝐝𝐢𝐧𝐠 𝐃𝐚𝐭𝐞 𝐟𝐫𝐨𝐦 𝐃𝐚𝐲 𝐨𝐟 𝐭𝐡𝐞 𝐘𝐞𝐚𝐫 𝐢𝐧 𝐄𝐱𝐜𝐞𝐥 𝐰𝐢𝐭𝐡 𝐅𝐨𝐫𝐦𝐮𝐥𝐚 & 𝐢𝐧 𝐏𝐨𝐰𝐞𝐫 𝐁𝐈 𝐮𝐬𝐢𝐧𝐠 𝐃𝐀𝐗
Imagine you have only Day of the Year in your Data and out of that if you wish to create
a complete Date, then this formula is useful !!
Excel - Add 54 in Cell A2 ( Why 54 , this is the Day of year on 23 Feb 2022)
in Cell B2 use this formula
𝐃𝐀𝐓𝐄(𝐘𝐄𝐀𝐑(𝐓𝐎𝐃𝐀𝐘()),𝐌𝐎𝐍𝐓𝐇(𝐓𝐎𝐃𝐀𝐘())-𝟏,𝐀𝟐)
You will get the result as Today's Date
Power BI - Create a New Column and the DAX will be
(Lets assume the name of the existing Column containing Day of Year is [Day of Year])
𝐑𝐞𝐪𝐮𝐢𝐫𝐞𝐝 𝐃𝐚𝐭𝐞 = 𝐃𝐀𝐓𝐄(𝐘𝐄𝐀𝐑(𝐓𝐎𝐃𝐀𝐘()),𝐌𝐎𝐍𝐓𝐇(𝐓𝐎𝐃𝐀𝐘())-𝟏, [𝐃𝐚𝐲 𝐨𝐟 𝐘𝐞𝐚𝐫])
the new calculated column Shows respective Date