𝐏𝐨𝐰𝐞𝐫 𝐁𝐈 𝐚𝐧𝐝 𝐄𝐱𝐜𝐞𝐥 - 𝐃𝐚𝐭𝐞 𝐓𝐢𝐩 - 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